Show Database last access time

Here is a script that I use to get info of when a database has last been accessed without having to set up a trace

Note : It gets it info from the DMV’s so if there is a server restart then the info is gone..  

— Get Last Restart time

SELECT

crdate
FROM
sysdatabases

WHERE name = ‘tempdb’

go

— get last db access time (Null = no access since last reboot)

SELECT name, last_access =(select X1= max(LA.xx)

from ( select xx =
max(last_user_seek)
where max(last_user_seek)is not null
union all
select xx = max(last_user_scan)
where max(last_user_scan)is not null
union all
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select xx =max(last_user_update)
where max(last_user_update) is not null) LA)
FROM master.dbo.sysdatabases sd
left outer join sys.dm_db_index_usage_statss
on sd.dbid= s.database_id
group by sd.name

Write a Comment

Your email address will not be published. Required fields are marked *