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 > 4AND [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 ServerLastRestartedFROM master..sysdatabasesWHERE name = 'tempdb'