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
 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
 (LastAccessDate FOR last_user_access IN
 , last_user_scan
 , last_user_lookup
 , last_user_update)
 ) AS Unpvt
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb')


Hope this helps.

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: Logo

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s