Pages

Social Icons

Wednesday 13 February 2013

SQL-Server :Script to see from how long database is not used



Hi,

Below script will help to find that from how long the database is not used.

SELECT ServerRestartedDate = (SELECT CREATE_DATE FROM sys.databases WHERE name='tempdb'), D.name,ds.last_read,ds.last_write FROM sys.databases d
INNER JOIN  (SELECT database_id,MAX(last_read) AS last_read,MAX(last_write) AS last_write FROM 
(SELECT database_id,last_user_seek AS last_read, NULL AS last_write FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT database_id,last_user_scan, NULL FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT database_id,last_user_lookup, NULL FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT database_id,NULL, last_user_update FROM sys.dm_db_index_usage_stats) A
GROUP BY database_id) ds
ON d.database_id = ds.database_id
ORDER BY name

Thx,
RS

No comments:

Post a Comment