How to Check Database Usage in SQL Server

Many a times, database developers and DBAs whom I meet, come up with a common question on how to check the databases in use or not. There are many ways to identity the database usage, but one of the best ways to find I felt was to use the system dmv to monitor the database usage in SQL Server.

Most of the times, in big organizations, a lot of databases will be created as part of request. Some of them can probably turn out to be a junk in the server. In case you come across any such instances to clean up the old databases that are not in use from quite long time, then simply use the dmv command as shown:

Select DBName,

MAX(LastAccessDate) DatabaseLastAccessedOn From (

Select DB_NAME(database_id) DBName,

last_user_seek

, last_user_scan

, last_user_lookup

, last_user_update

FROM sys.dm_db_index_usage_stats where DB_NAME(database_id) not in ('Tempdb','msdb','model','system')

) AS Pvt

UNPIVOT

(LastAccessDate FOR last_user_access IN

(last_user_seek

, last_user_scan

, last_user_lookup

, last_user_update)

) AS Unpvt

GROUP BY DBName

Order by 1

From the above SELECT command, the query checks for the last_user_seek, last_user_scan, last_user_lookup and last_userupdate for the respective databases in the server and produces the result.

Note: Use the code in jobs to schedule at different times to dump in a user table and select the distinct unused databases to get the best results.

You may also need:

"How to Set a Database to Read-Only in SQL Server"
"SQL Server - How to Shrink Big Log File Size of a Database"