database last accessed date time

database last accessed date time

Sometime you may required to know the last accessed date and time of database..
Below query returns the lass accessed date and time of SQL Database.


SELECT DatabaseName, MAX(LastAccessDate) LastAccessDate
FROM
    (SELECT
        DB_NAME(database_id) DatabaseName
        , last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update
    FROM sys.dm_db_index_usage_stats) AS PivotTable
UNPIVOT 
    (LastAccessDate FOR last_user_access IN
        (last_user_seek
        , last_user_scan
        , last_user_lookup
        , last_user_update)
    ) AS UnpivotTable
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'model', 'msdb', 'tempdb')
ORDER BY MAX(LastAccessDate)

The biggest problem of above query is that all information will be lost when you restart SQL Server. So, this is not applicable for servers which frequently restarted.