Often DBAs get requests to identify when a database was last accessed in an environment. These requests are more common in development or test environments where developers are making changes constantly and lose track of when a specific change was made. In this post the focus is on identifying when a user database was last accessed. The script uses sys.dm_db_index_usage_stats which gives us statisical information of indexes. Please find the script below:
SELECT DatabaseName, MAX(LastAccessDate) DatabaseLastAccessedOn 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 Pvt UNPIVOT (LastAccessDate FOR last_user_access IN (last_user_seek , last_user_scan , last_user_lookup , last_user_update) ) AS Unpvt GROUP BY DatabaseName HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb') ORDER BY 2
Hope this helps.