Wednesday, February 25, 2015

When was SQL Server database Last used??

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