If we want to remove the databases from the server which are not getting used from a long time. In this case first we need the name of databases which are not used from a long time. We can use below script to find the same.
SELECT DBName, MAX(LastUsedDate) LastUsedDate
FROM
(SELECT
DB_NAME(database_id) DBName
, last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update
FROM sys.dm_db_index_usage_stats) AS PvtTable
UNPIVOT
(LastUsedDate FOR last_user_access IN
(last_user_seek
, last_user_scan
, last_user_lookup
, last_user_update)
) AS UnpvtTable
GROUP BY DBName
ORDER BY LastUsedDate
Thanks!!
No comments:
Post a Comment