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' |