March 30, 2011 at 12:12 pm
March 31, 2011 at 1:41 am
You could check index access statistics for the tables in the database:
DECLARE @accessStats TABLE (
db_name sysname,
last_access datetime
)
INSERT @accessStats
EXEC sp_msForEachDb '
SELECT ''?'', MAX(last_access) AS last_access
FROM (
SELECT DB_NAME() AS db_name,
MAX(last_user_seek) AS last_user_seek,
MAX(last_user_scan) AS last_user_scan,
MAX(last_user_lookup) AS last_user_lookup,
MAX(last_user_update) AS last_user_update
FROM [?].sys.dm_db_index_usage_stats AS s
) AS src
UNPIVOT (
last_access
FOR access_type IN (
last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update)
) as u
GROUP BY db_name
'
SELECT *
FROM @accessStats
Hope this helps
Gianluca
-- Gianluca Sartori
March 31, 2011 at 6:51 am
Nice usage of a pivot there Gianluca, I really need to get PIVOT into my scripting usage a bit more.
I built something similar without the pivot, yours makes it very clean;
only thing i did different now is i added the server reboot date, so you have some reference for how long it's been since the database was accessed.:
SELECT
stat.*,
ServerRestarted.ServerRebootedOn
FROM @accessStats stat
CROSS JOIN
(SELECT
CREATE_DATE As ServerRebootedOn
FROM sys.databases
WHERE name='tempdb') ServerRestarted
Lowell
June 3, 2015 at 12:47 pm
Nice One
Thanks.
May 30, 2021 at 8:55 pm
Try this, if the last_access column is null then no reads or writes have occurred:
WITH cte AS (
SELECT database_id, dt, op
FROM sys.dm_db_index_usage_stats
UNPIVOT (dt for op in (last_user_lookup, last_user_scan, last_user_seek, last_user_update)) b)
SELECT d.name DB, MAX(c.dt) last_access, MAX(i.sqlserver_start_time) sqlserver_start_time, GETUTCDATE() captured
FROM sys.databases d
LEFT JOIN cte c ON d.database_id=c.database_id
CROSS JOIN sys.dm_os_sys_info i
WHERE d.database_id>4
GROUP BY d.name
ORDER BY d.name;
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy