SQL Stuff

Find When Databases Were Last Accessed

Every now and then you’ll need to see when a database was last accessed. Without setting up an auditing solution directly you can find out when the point in time was that the last access occurred by looking at the sys.dm_db_index_usage_stats. Here’s some logic to take the results of using this and packaging it into a tidy little temp table.

This runs across every database, and reports back the following:

Server Name

Database Name

Last Seek

Last Scan

Last Lookup

Last Update

use master;

set nocount on

if object_id('tempdb..#last_accessed_times') is not null

drop table #last_accessed_times

create table #last_accessed_times

(

[server_name] varchar(255)

, [database_name] varchar(255)

, [last_user_seek] datetime

, [last_user_scan] datetime

, [last_user_lookup] datetime

, [last_user_update] datetime

)

declare @get_last_accessed_time varchar(max)

set @get_last_accessed_time = ''

select @get_last_accessed_time = @get_last_accessed_time +

'use [' +  sd.name + '];' + char(10) +

'select ' + char(10) + '

''Server Name'' = @@servername ' + char(10) + '

, ''Database Name'' = upper(db_name(db_id())) ' + char(10) + '

, ''Last User Seek'' = max(sddius.last_user_seek) ' + char(10) + '

, ''Last User Scan'' = max(sddius.last_user_scan) ' + char(10) + '

, ''Last User Lookup'' = max(sddius.last_user_lookup) '+ char(10) + '

, ''Last User Update'' = max(sddius.last_user_update) '+ char(10) + '

from

sys.dm_db_index_usage_stats sddius

where

sddius.database_id = db_id()' + char(10)

from master.sys.databases sd

where sd.database_id > 4

order by sd.name asc

insert into #last_accessed_times

exec (@get_last_accessed_time)

select

[server_name] = upper([server_name])

, [database_name] = upper([database_name])

, [last_user_seek] = left([last_user_seek], 19)

, [last_user_scan] = left([last_user_scan], 19)

, [last_user_lookup] = left([last_user_lookup], 19)

, [last_user_update] = left([last_user_update], 19)

from

#last_accessed_times

order by

[server_name]

, [database_name] asc

drop table #last_accessed_times

Here’s a sample of the results. Just look at the Last_User_Seek column. The dates in this case have been converted using a more readily identified date (Human Readable)

Advertisements

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s