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
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