How to find if the database is not being used

Question

Post reply

How to find if the database is not being used

WhiteLotus

SSChampion

Points: 11963
More actions
September 26, 2021 at 10:22 pm

#3933273

Hi All,

I want to check if the database is not being used and the last transaction into that database

Any query ?

your feedback is much appreciated
September 27, 2021 at 8:24 am

This was removed by the editor as SPAM

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Alejandro Santana SSCommitted Points: 1828 More actions · Answer 1

Alejandro Santana

SSCommitted

Points: 1828

September 26, 2021 at 10:54 pm

#3933278

The way we do it is that we don't check when was the last transaction, we put an extended event capturing anything that goes into the database and we let it run for a month at least, if nobody is using it, the extended event won't grow that much so it shouldn't matter.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 2

Alejandro Santana wrote:

The way we do it is that we don't check when was the last transaction, we put an extended event capturing anything that goes into the database and we let it run for a month at least, if nobody is using it, the extended event won't grow that much so it shouldn't matter.

Ok... so what happens that event capturing "anything" if someone is using it in a very active fashion?

I've not tried it because I know the owners of all of our databases and just ask them but, couldn't you do a DBCC FREEPROCCACHE and then occasionally go and look to see if any new plans have been cached that aren't maintenance utilities like backups, etc? That would also give you an idea of who is using it for what and you don't actually have to collect any additional data. It's something the system does on it's own.

For that matter, you could even start out by looking at just sys.dm_db_index_usage_stats with the understanding that it is totally reset every time the SQL Server Service is started.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

Alejandro Santana SSCommitted Points: 1828 More actions · Answer 3

That's actually a good idea Jeff, thanks 🙂

In our case when it happens its usually in old applications and we want to know which user is using it since in these old applications the users are actually created as SQL Logins (forgot to explain this part ) then we ask these users why they still use this application if its not supposed to be used because it was replaced by a new system and stuff like that, sometimes we event get asked by IT Sec who uses it and for what purposess.

after all these steps are taken and we have the green light we proceed to backup one last time and delete it.

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 4

I figured it might be better than my BSOFH method which is to take the database offline and wait for the email. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

david.edwards 76768 Ten Centuries Points: 1246 More actions · Answer 5

Jeff Moden wrote:

I figured it might be better than my BSOFH method which is to take the database offline and wait for the email. 😀

I thought that was accepted "Best Practice", is it not? 😉

"Knowledge is of two kinds. We know a subject ourselves, or we know where we can find information upon it. When we enquire into any subject, the first thing we have to do is to know what books have treated of it. This leads us to look at catalogues, and at the backs of books in libraries."
— Samuel Johnson
I wonder, would the great Samuel Johnson have replaced that with "GIYF" now?

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 6

david.edwards 76768 wrote:

Jeff Moden wrote:

I figured it might be better than my BSOFH method which is to take the database offline and wait for the email. 😀

I thought that was accepted "Best Practice", is it not? 😉

Might as well be. 😀 There will always be someone that pipes up 6 months later and accusing us of that so just get it out of the way early. 😀

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions

stevec883 SSC-Addicted Points: 439 More actions · Answer 7

Table stats is the first thing that came to mind as well. It's kind of amazing that there is no feature in SSMS to get more details of which logons have accessed tables, data, indexes etc. We have to use such a round about way, look at stats and then, from that, deduce that someone was in there.

This reply was modified 3 years ago by stevec883.