Your boss walks up to you one morning and says, “Hey, I wanna list of all of our databases and when they were last accessed”.
If you’ve got some sort of auditing switched on or a trace or xevent catching this sort of info you might be ok, but I’m betting you don’t have any of that. That’s cool, it’s not something that I tend to monitor as standard either.
But if you’re not monitoring it, is there any way that you can get at that info? SQL doesn’t really give us anything obvious here, there’s no magic, ‘last_access_date’ column in sys.databases or anywhere else for that matter.
There may be a way around this problem though, check out sys.dm_db_index_usage_stats. You’ll see some interesting columns, last_user_seek, last_user_scan, last_user_lookup and last_user_update for each index in a database.
Those cover just about any interaction that a user is going to have with an index, take the highest value from each of those columns per database and you’re going to have a very good indication of when the database was last accessed.
The below code will give you the latest date that any index in a database was touched and so indicate when the database was last accessed.
SELECT db_name(databases.database_id) AS DBName,
(SELECT MAX(last_user_access)
FROM (
VALUES (MAX(last_user_seek))
,(MAX(last_user_scan))
,(MAX(last_user_lookup))
) AS value(last_user_access)
) AS last_user_access
FROM sys.dm_db_index_usage_stats indexstats
RIGHT OUTER JOIN sys.databases databases ON indexstats.database_id = databases.database_id
GROUP BY databases.database_id
ORDER BY db_name(databases.database_id)
Note, sys.dm_db_index_usage_stats like many DMVs is cleared out on a SQL restart. Any NULL value for last_user_access indicates that the database hasn’t been accessed since the last restart. Unfortunately there’s no way to get back any dates prior to a restart. Hmmm that could be a nice addition to our Undercover Catalogue tool, watch this space on that one.
Thanks for reading and I hope you find this useful.