Monday, January 21, 2013

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_stats s
on sd.dbid= s.database_id
group by sd.name


2 comments:

microsoft certifications said...

Hey very nice blog!!
Hi there,I enjoy reading through your article post, I wanted to write a little comment to support you and wish you a good continuationAll the best for all your blogging efforts.
Appreciate the recommendation! Let me try it out.
Keep working ,great job!
http://www.sqlservermasters.com/

Hagen Deike said...

Just giving an update. On SQL server 2008 R2, getting the last reboot time:

SELECT
create_date
FROM
sys.databases
WHERE name = 'tempdb'

go