Get Unused Databases
Applicability:
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:
Credits:
Author:ChillyDBA
Date: 6 Jun 2020
Description
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
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'
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'