Databases Last Accessed

 Running the script below will show you the last access date and time of all databases since the day sql server was rebooted

-- Get Last Restart time
SELECT
crdate as 'Last Rebooted On'
FROM
sysdatabases
WHERE name = 'tempdb'
go
 
-- Get last database 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
ORDER BY name

1 comment:

  1. I wanted to thank you for this great Information and knowledge, I definitely loved every little bit of it. I have you bookmarked your web site to check out the latest stuff you post. Your blog is eye-catching. I get pleasure from it. Thanks for sharing this beautiful piece of writing with me, get more information at alteryx certification exam questions for more Analytics Fun related information and knowledge.

    ReplyDelete