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
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.
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
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.
15 People are following this question.
