question

ruancra avatar image
ruancra asked

Database Last Activity Date

Hi all Is there a way to check when last a database was in use? We set up a new QA environment some time ago, restored a lot of database onto it, but i think many of the databases are not being used. I want to check the last date of any activity done in a database. Thanks
tsqlsql2008r2
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

DenisT avatar image
DenisT answered
I wonder if you could use sys.dm_db_index_usage_stats -- if no indexes are being used in a database, the database is not being used. Just keep in mind, this DMV is reset after your SQL instance is restarted.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

I did query that DMV: Select database_id ,DB_NAME(database_id) as db ,max(last_user_seek) as last_user_seek ,max(last_user_scan) as last_user_scan ,max(last_user_update) as last_user_update from sys.dm_db_index_usage_stats group by database_id ,DB_NAME(database_id) This seems to work
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
I don't know if there's anything more scientific but you could check the last updated date on the data files in Windows Explorer. As long as you don't have maintenance jobs that update the database that should be fairly reliable. Certainly if the date is old, nobody has altered any data since then.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.