Few days back, one of my friends asked me whether there is a way to find the last accessed date of their SQL Server databases. What he really wanted was, finding out databases that are not being used. Nothing came into my mind immediately. I tried to remember a catalog view, a dynamic management function for this but nothing popped up.
Few things came to my mind later. First thing was SQL Server Auditing. I thought that there will be an AUDIT GROUP for database login. Bad luck, it is only available at server level only. I found out few posts related to this who have used DATABASE-LEVEL groups to track statements like SELECT, and most of the posts are for seeing the last access for tables, not for the database. Again, I thought it is bit difficult to implement, because my friend manages more than 60 databases :). So, I gave up the idea of using SQL Server Auditing for finding out the last accessed time for databases. If anyone has used auditing for seeing the last accessed date for databases, please let me know the way. Anyway, I ended up with this, though it has a problem too.
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', 'tempdb', 'model', 'msdb')
ORDER BY 2
What I have used here is sys.dm_db_index_usage_stats. This goes through all indexes (including heap) and shows last accessed time for them. By looking at these accessed time, we can determine the last accessed time for the databases, and can see whether the databases are being used or not. Here what I see when I run the script:
Biggest problems with this technique is, if the SQL Server service is restarted, everything is reset. So, this is not applicable for servers which get restarted frequently. Other problem is related to Full-Text-Catalogs. If you have enabled Full-Text-Index on your tables, you might see a record saying that database has been accessed without any user operations. This is because of Full-Text-Indexes.
Hi,
ReplyDeleteHow can I make it work in SQL 2000?
thanks
Hi Roopie,
ReplyDeleteUnfortunately the function I have used is not available with SQL Server 2000. I think that only way is using sysprocesses table but you will face for same issue as I discussed with my post, if the service is restarted.
Thank!
ReplyDeleteWe have a developer environment where all the developers are able to create their own test databases.
This script comes in handy as they are not always as good a cleaning up :-)
Hey, such an old posting ... and still the best, compared to others, because you also mention drawbacks
ReplyDeleteOfftopic: i like the clear layout of your blog :-)
This query giving the last user scan details for current day only,if we didnt use any database today, then that database details are not coming, how to do this?
ReplyDelete
ReplyDeleteHi Gorgonz,
Thanks for the post.
Hi Chaintanya,
It is not for current day. It basically gives you last accessed date of SQL Server Database from the last restart of the service. Example, if the last restart date is 01-Mar-2014 and last accessed date of a database called DB1 is 10-Mar-2014, then this query shows the date for DB1 as 10-Mar-2014.
Are you getting a different result?
Would this work if auto update stats is enabled? For some reason, the LastAccessDate is updating at the exact same time for all the databases, every 10 or 15 min or so. Thanks!
ReplyDeleteHi Dinesh,
ReplyDeleteI am getting the following error when running your script:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'LastAccessDate'.
Msg 156, Level 15, State 1, Line 9
Incorrect syntax near the keyword 'AS'.
Thanks
Rahim
Hi Rahim,
ReplyDeleteI just ran the same against SQL 2014, had no issue, please check whether you have missed something when copying it.
Regards
Dinesh
Hi Dinesh. Excellent query. If I wanted to get the inverse of this (i.e. databases that have NOT been accessed since the last reboot), how would I code that?
ReplyDeleteThis comment has been removed by the author.
ReplyDeleteHi Ian,
ReplyDeleteThanks for the comment. Check this query, did not thoroughly check but it should give you the required result;
SELECT name DatabaseName
FROM sys.databases
WHERE name NOT IN ('master', 'tempdb', 'model', 'msdb')
EXCEPT
SELECT DISTINCT
DB_NAME(database_id) DatabaseName
FROM sys.dm_db_index_usage_stats
ORDER BY 1
Hi,
ReplyDeleteI have nearly 50 databases attached to my Server and when I run this query I don't get any results.
If I comment out the system databases, then the only result I get is for msdb.
Any idea of what would cause this?
Thanks.
Hi Chris,
ReplyDeleteAre you referring the code in the post (that shows last accessed date) or in the comment (that shows databases not accessed since last restart)? If you are referring the first, just run SELECT * FROM sys.dm_db_index_usage_stats and see whether you get any records. Generally, you should get a result, let's see if you not.
If you refer the second query, and no result, it means that all databases have been accessed since last restart.
Hi Dinesh,
ReplyDeleteThanks for the quick reply :)
I was referring to the query in your original post. Since my first comment, I've attached a new database and it appears in the list, likewise if I hit one of the other databases it appears in the results.
So I think for some reason on my server the sys.dm_db_index_usage_stats table is getting cleared overnight - as I know for sure that I accessed some of the other yesterday - any idea what would cause this?
Thanks.
Hi Chris,
ReplyDeleteI think that you have set AUTO_CLOSE property of the database as TRUE. This is one of the triggers for clearing captured counters in index_usage_stats. Please check and see.
Doesn't work on
ReplyDeleteMicrosoft SQL Server Management Studio 13.0.16106.4
Microsoft Analysis Services Client Tools 13.0.1700.441
Microsoft Data Access Components (MDAC) 10.0.15063.0
Microsoft MSXML 3.0 4.0 6.0
Microsoft Internet Explorer 9.11.15063.0
Microsoft .NET Framework 4.0.30319.42000
Operating System 6.3.15063
It returns me the error:
Msg 156, Level 15, State 1, Line 13
Incorrect syntax near the keyword 'AS'.
Hi Francesco,
ReplyDeleteI just tested with SSMS and my version is 14.0.17177.0. It works fine, please check the code again, you might have missed some when copying and pasting the code.
If you still experiencing, send me a screenshot, will have a look.
Regards
Dinesh