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

Steve Jones - Editor gravatar image

Steve Jones - Editor ♦♦
5.1k 78 79 82

(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

Tom Staab gravatar image

Tom Staab
5.8k 6 8 10

I should clarify that you obviously don't need to check sys.objects to determine when the last query was run, but I wanted to add it because someone could modify an object after the last query finished.
Oct 29, 2009 at 01:14 PM Tom Staab
I really like this - +1
Oct 29, 2009 at 04:08 PM Matt Whitfield ♦♦
The only issue is that sys.dm_db_index_usage_stats are cleared when the SQL server is restarted. Qoute from books online "The counters are initialized to empty whenever the SQL Server (MSSQLSERVER) service is started"
Oct 30, 2009 at 03:53 AM Håkan Winther
(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

Håkan Winther gravatar image

Håkan Winther
15.7k 35 37 48

I decided not to go there because I wanted my answer to work for 2005 also. I had planned to mention the 2008 auditing capabilities at the end but forgot, so I'm glad you did.
Oct 29, 2009 at 02:44 PM Tom Staab
You are absolutely right, your answer works in both SQL 2005 and 2008, and I am too spoiled with the possibility to use the latest (Enterprice) version of SQL in my projects. But, you also need to store your result in a table because of the sys.dm_db_index_usage_stats is cleared upon restart, audit files are not.
Oct 30, 2009 at 04:04 AM Håkan Winther
Very good point. Funny story: A few years ago, I designed a system (table, procedure, job, etc.) to track various server and database statistics for maintenance. After several months of part-time work adjusting and enhancing it, I decided I would try to package it for general distribution. About a week later, I learned about Microsoft's auditing plans for SQL Server 2008.
Oct 30, 2009 at 04:53 AM Tom Staab
Tom, Thats the sad part with inventing the wheel to someone else vehicle. :)
Oct 30, 2009 at 04:57 AM Håkan Winther
(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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x126

asked: Oct 29, 2009 at 12:32 PM

Seen: 6354 times

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