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
1 comment:
Just giving an update. On SQL server 2008 R2, getting the last reboot time:
SELECT
create_date
FROM
sys.databases
WHERE name = 'tempdb'
go
Post a Comment