How to find last database access date/time?
-
Friends,
Would need your help for the situation below:
I have a development server with many DB instances. I would like to find out which DB's have not been accessed since 15 days and would shut-down those DB's.
I have created a query below to find out this information using DMV - dm_db_index_usage_stats
WITHBase
AS (SELECT Db_name(database_id) DatabaseName,
last_user_seek AS last_access_time
FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT Db_name(database_id) DatabaseName,
last_user_scan
FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT Db_name(database_id) DatabaseName,
last_user_lookup
FROM sys.dm_db_index_usage_stats
UNION ALL
SELECT Db_name(database_id) DatabaseName,
last_user_update
FROM sys.dm_db_index_usage_stats),
AccessInfo
AS (SELECT DatabaseName,
Datediff(dd, Max(last_access_time), Getdate()) AS no_of_days
FROM Base
GROUP BY DatabaseName
HAVING DatabaseName NOT IN ('master', 'tempdb', 'model', 'msdb'))
SELECT *
FROM AccessInfo
where no_of_days > 15
ORDER BY 2;
Though this query works fine, but the major drawback is if the server restarts, all index stats are cleaned-up. Is there any other good option of working with this requirement? Would really appreciate your generous help.
Thank you.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
That's the best you can do without custom auditing. The information you want is not kept by SQL by default.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
Correct!
Is there some way Logon Trigger be used to ascertain this information. I know Logon trigger operate on sever level, still checking if that can be tweaked in some way:-)
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
Lokesh
Try dumping the results of your query into an audit table every five minutes. You'll want to test and plan carefully so that you don't end up using lots of disk space or affecting performance.
The problem with login triggers is that you don't know at login time what databases the user is going to access.
John
-
Lokesh Vij (12/11/2012)
Is there some way Logon Trigger be used to ascertain this information.No. You're not looking for login information, you're looking for database access information. If you were to check what DB the user logs into at login time only, then you completely miss when they change DB and access another couple.
The other thing that you might be able to do is put down an extended events session, probably with a ring buffer that you poll or with a bucketiser that records shared database locks.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
John Mitchell-245523 (12/11/2012)
Try dumping the results of your query into an audit table every five minutes. You'll want to test and plan carefully so that you don't end up using lots of disk space or affecting performance.Paraphrasing for clarity - you mean to say that dumping the result of all queries made by the users in audit table?
The problem with login triggers is that you don't know at login time what databases the user is going to access.
Yes, that's correct!
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
No. The query that you posted above, run it every five minutes and insert the results into your audit table. Bear in mind that if a database is accessed between the query running and server shutdown (or any other event that clears the index use information) then you'll lose that information.
John
-
Thanks Gail!
The other thing that you might be able to do is put down an extended events session, probably with a ring buffer that you poll or with a bucketiser that records shared database locks.
I will try using extended events! Your help is much appreciated..
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
-
John Mitchell-245523 (12/11/2012)
No. The query that you posted above, run it every five minutes and insert the results into your audit table. Bear in mind that if a database is accessed between the query running and server shutdown (or any other event that clears the index use information) then you'll lose that information.John
Cool. Makes sense!
Thanks John for your help. Much appreciated.
~ Lokesh Vij
Guidelines for quicker answers on T-SQL question[/url]
Guidelines for answers on Performance questionsLink to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply