Search code examples
sql-server-2005

Finding unused SQL Server databases

Is there any way to find unused SQL Server 2005 databases ?

I'm in the process of upgrading and migrating my server into SQL Server 2008 x64 in new server instance from 2005 32 bit.

Solution

  • Try this: if the last_access column is null then no reads or writes have occurred:

    WITH cte AS (
SELECT database_id, dt, op 
FROM sys.dm_db_index_usage_stats
    UNPIVOT (dt for op in (last_user_lookup, last_user_scan, last_user_seek, last_user_update)) b)
SELECT d.name DB, MAX(c.dt) last_access, MAX(i.sqlserver_start_time) sqlserver_start_time, GETUTCDATE() captured
FROM sys.databases d 
LEFT JOIN cte c ON d.database_id=c.database_id
CROSS JOIN sys.dm_os_sys_info i
WHERE d.database_id>4
GROUP BY d.name 
ORDER BY d.name;