Get Unused Databases

Applicability:

                 SQL Server 2000:        N/A
                 SQL Server 2005:        Tested
                 SQL Server 2008:        Tested
                 SQL Server 2008R2:    Tested
                 SQL Server 2012:        Tested
                 SQL Server 2014:        Tested
                 SQL Server 2016:        Tested
                 SQL Server 2017:        Not Tested          

Credits:

Author:ChillyDBA
Date:    6 Jun 2020

Description

Get a list of databases on the source server that have not been used since the last SQL Server restart.
Useful for identifying potentially obsolete DBS that do not need to be migrated

GENERATE script on:     SOURCE SERVER
EXECUTE script on:       n/a

Code


-- get a list of databases not used since the server was last restarted SELECT * FROM sys.databases WHERE database_id > 4 AND [name] NOT IN ( SELECT DB_NAME(database_id) FROM sys.dm_db_index_usage_stats WHERE COALESCE(last_user_seek, last_user_scan, last_user_lookup,'1/1/1970') > ( SELECT login_time FROM sys.sysprocesses WHERE spid = 1 ) ) SELECT crdate AS ServerLastRestarted FROM master..sysdatabases WHERE name = 'tempdb'





ċ
Andy Hughes,
Jun 6, 2020, 10:45 AM
Comments