Hi,
we were working on clean up our servers , so on that we would like to find out what all databases were not in use..
How do we find out whether SQL databases were used by anyone ?
Can you please help me on that .
Thanks,
Hi,
we were working on clean up our servers , so on that we would like to find out what all databases were not in use..
How do we find out whether SQL databases were used by anyone ?
Can you please help me on that .
Thanks,
I've used this query to find databases with no read/write access:
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;
If the last_access column is null then no reads or writes have occurred. You should run it regularly for a period of time (e.g. daily for at least 1 month) before you determine which databases to drop.