Script to identify when a user database was last accessed

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.

Advertisements
This entry was posted in SQL Server DBA Stuff and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s