How to determine if a DB is currently being used

Question

Post reply

How to determine if a DB is currently being used

dax.latchford

SSCommitted

Points: 1969
More actions
October 30, 2024 at 4:17 pm

#4475517

Hi - I'm looking for advice regarding the best & quickest way to establish whether or not a SQL2012 DB is being used.
Many thanks
Dax

Viewing 5 posts - 1 through 4 (of 4 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736057 More actions · Answer 1

As in is a user connected? Or has anyone been running queries. Those are two different things.

I believe when someone connects, sp_who2 shows they are in the database, and I believe there is a shared lock.

If you want to know if anyone has used the database in the last xxx days, you likely need an Extended Event trace looking for any activity in that database.

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

You can have a look at sys.dm_db_index_usage_stats() to see if there have been any reads or writes since the last time the SQL Server Service was started.

Once you've decided that no on is using it, don't just drop it. Just take it offline and wait for 6 weeks to see if the proverbial phone rings.

--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)

Johan Bijnens SSC Guru Points: 135352 More actions · Answer 3

Enable "auto close" for that database and monitor the sql server errorlog to see when it is being brought online.

ALTER DATABASE [Yourdb] SET AUTO_CLOSE ON WITH NO_WAIT ;

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

Grant Fritchey SSC Guru Points: 398872 More actions · Answer 4

Personally (and I can hear the collective groans), I'd put Extended Events to work on the database. Capture all queries run against. The reason I say this, index usage stats will tell you if the database is being touched... but not by whom or why? Maybe you have an automated thing that's doing something, who knows what, but not a single person is ever touching the database. Capturing the queries lets you know what's going on. rpc_completed and sql_batch_completed, both filtered for the database in question.

Then, 100% with Jeff, take it offline for some weeks, see who screams, before you drop it.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning