Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to find last database access date/time? Expand / Collapse
Author
Message
Posted Tuesday, December 11, 2012 2:24 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:24 AM
Points: 1,176, Visits: 1,253
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
WITH 
Base
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1394953
Posted Tuesday, December 11, 2012 2:36 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 37,741, Visits: 30,020
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1394963
Posted Tuesday, December 11, 2012 2:38 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:24 AM
Points: 1,176, Visits: 1,253
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1394965
Posted Tuesday, December 11, 2012 2:47 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 4,434, Visits: 7,218
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
Post #1394969
Posted Tuesday, December 11, 2012 2:51 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 4:11 PM
Points: 37,741, Visits: 30,020
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 2008, MVP
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Post #1394972
Posted Tuesday, December 11, 2012 2:54 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:24 AM
Points: 1,176, Visits: 1,253
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1394974
Posted Tuesday, December 11, 2012 2:59 AM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 8:44 AM
Points: 4,434, Visits: 7,218
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
Post #1394979
Posted Tuesday, December 11, 2012 2:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:24 AM
Points: 1,176, Visits: 1,253
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1394980
Posted Tuesday, December 11, 2012 3:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 8:24 AM
Points: 1,176, Visits: 1,253
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter

Post #1394983
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse