Decommissioning Old Databases

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:

    TOP 10000 s.[database_id]
    [master].[sys].[dm_db_index_usage_stats] as s right outer join sys.databases d on s.database_id = d.database_id
    d.database_id > 4
order by

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.

comments powered by Disqus