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 8 posts - 1 through 7 (of 7 total)

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

Steve Jones - SSC Editor SSC Guru Points: 736478 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: 1004406 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: 135376 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: 398909 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

Steve Jones - SSC Editor SSC Guru Points: 736478 More actions · Answer 5

Steve Jones - SSC Editor

SSC Guru

Points: 736478

October 31, 2024 at 2:01 pm

#4475933

Oooh, I like the autoclose. That's an easy one.

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 6

Any connection accessing a database will, at the very least, hold an S (Share) lock on the database. That S lock prevents others from taking exclusive control of the DB while it is in use.

All active locks may be viewed with the sys.dm_tran_locks DMV.

To simply see if a database is in use:

DECLARE @DBToCheck sysname = '<your_db_name>';
IF EXISTS(SELECT 1 FROM sys.dm_tran_locks 
           WHERE resource_type = 'DATABASE' 
             AND resource_database_id = DB_ID(@DBToCheck))
  SELECT @DBToCheck + ' is in use.' AS [Result];
ELSE
  SELECT @DBToCheck + ' is not in use.' AS [Result];

To see who is using the DB, when they last used it, and if there are any active transactions:

DECLARE @DBToCheck sysname = '<your_db_name>';
SELECT tl.request_session_id, tl.request_mode, tl.request_type, tl.request_status,
       s.login_name, s.[host_name], s.open_transaction_count, c.last_read, c.last_write, 
       s.last_request_start_time, s.last_request_end_time
  FROM sys.dm_tran_locks       tl
  JOIN sys.dm_exec_connections c ON tl.request_session_id = c.[session_id]
  JOIN sys.dm_exec_sessions    s ON tl.request_session_id = s.[session_id]
 WHERE resource_type = 'DATABASE' AND resource_database_id = DB_ID(@DBToCheck);

This reply was modified 1 month, 2 weeks ago by Eddie Wuerch. Reason: added BOL link to sys.dm_tran_locks

Eddie Wuerch
MCM: SQL

jonathan.ellison SSC Enthusiast Points: 148 More actions · Answer 7

Here is a simple query I run to see when a database was last accessed.

SELECT TOP 10000 s.[database_id]

,d.name

,d.create_date

,s.[user_seeks]

,s.[user_scans]

,s.[user_lookups]

,s.[user_updates]

,s.[last_user_seek]

,s.[last_user_scan]

,s.[last_user_lookup]

,s.[last_user_update]

FROM [master].[sys].[dm_db_index_usage_stats] as s right outer join sys.databases d on s.database_id = d.database_id

where d.database_id > 4

order by d.name