Wednesday, October 12, 2016

Find SQL Server Databases that are not accessed after last reboot

I have written a post tilted When was my SQL Server Database last accessed? that talks about how to find the last accessed date and time of databases. As per one of the comments, it is important to find out databases that were not accessed after last reboot as well, hence wrote this query for that.

It may be helpful to you.

SELECT name DatabaseName
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb') -- and if you other system DBs
EXCEPT
SELECT DISTINCT
 DB_NAME(database_id) DatabaseName
FROM sys.dm_db_index_usage_stats
ORDER BY 1


No comments: