Occasionally I inherit an old development database server containing many databases and I usually have no idea which are no longer in use. I came across the following script that allows you to see the last time a database was accessed:
SELECT
TOP 10000 s.[database_id]
,d.name
,d.create_date
,s.[index_id]
,s.[object_id]
,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
This produced the following results:
I was concerned about the null values for one of the databases because it seemed strange for a database to be created but never accessed, but this can happen, especially in dev environments. To test this I created a small Entity Framework Core console app to simulate an application accessing the data and then re-ran the query:
Looks like it works perfectly, I can now decommission a few databases to tidy up the server and sleep a bit easier tonight. Now must remember to keep the backup in a safe place should anyone start screaming for the database they created and never accessed.