x

How do I get the last time a database was accessed?

I have a number of old databases and I am not sure if they are still in use. How can I determine the last time that a database had a query run in it?

more ▼

asked Oct 29, 2009 at 12:32 PM in Default

avatar image

Steve Jones - Editor ♦♦
5.1k 79 93 87

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

I decided to make it a scalar function. This checks all index (including heap) usage statistics and all object modification times.

CREATE FUNCTION GetDatabaseLastAccessedDTM ( @DatabaseName sysname ) RETURNS datetime AS BEGIN -- Declare the return variable here DECLARE @ResultVar datetime

 DECLARE @DatabaseId int; SET @DatabaseId = (SELECT d.database_id FROM sys.databases d WHERE d.name=@DatabaseName);

 SELECT @ResultVar = MAX(last_accessed_dtm) FROM
 (
     SELECT MAX(t.last_system_lookup) AS last_accessed_dtm
     FROM sys.dm_db_index_usage_stats t WHERE t.database_id = @DatabaseId
     UNION
     SELECT MAX(t.last_system_scan) FROM sys.dm_db_index_usage_stats t WHERE t.database_id = @DatabaseId
     UNION
     SELECT MAX(t.last_system_seek) FROM sys.dm_db_index_usage_stats t WHERE t.database_id = @DatabaseId
     UNION
     SELECT MAX(t.last_system_update) FROM sys.dm_db_index_usage_stats t WHERE t.database_id = @DatabaseId
     UNION
     SELECT MAX(t.last_user_lookup) FROM sys.dm_db_index_usage_stats t WHERE t.database_id = @DatabaseId
     UNION
     SELECT MAX(t.last_user_scan) FROM sys.dm_db_index_usage_stats t WHERE t.database_id = @DatabaseId
     UNION
     SELECT MAX(t.last_user_seek) FROM sys.dm_db_index_usage_stats t WHERE t.database_id = @DatabaseId
     UNION
     SELECT MAX(t.last_user_update) FROM sys.dm_db_index_usage_stats t WHERE t.database_id = @DatabaseId
     UNION
     SELECT MAX(o.modify_date) FROM sys.objects o
 ) t

 -- Return the result of the function
 RETURN @ResultVar

END GO

Now just call the function for any database name.

SELECT dbo.GetDatabaseLastAccessedDTM('master')
more ▼

answered Oct 29, 2009 at 01:12 PM

avatar image

Tom Staab ♦
14.5k 7 14 18

(comments are locked)
10|1200 characters needed characters left

I have migrated an old database to SQL server 2008 and no one knows if every object in the database is in use anymore. I activated database audit to log "object access" and use the "audit file" to update "last accessed" column in a table with all user objects. If the object has not been used for a long time we rename it in our acceptance test server.

Read more about auditing in http://msdn.microsoft.com/en-us/library/dd392015.aspx

more ▼

answered Oct 29, 2009 at 02:37 PM

avatar image

Håkan Winther
16.4k 36 45 57

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x131

asked: Oct 29, 2009 at 12:32 PM

Seen: 6999 times

Last Updated: Oct 29, 2009 at 12:32 PM

Copyright 2016 Redgate Software. Privacy Policy