Get Microsoft SQL Server database last activity time
WITH LastActivity AS
(
SELECT
database_id,
MAX(last_user_seek) AS last_seek,
MAX(last_user_scan) AS last_scan,
MAX(last_user_lookup) AS last_lookup,
MAX(last_user_update) AS last_update
FROM sys.dm_db_index_usage_stats
GROUP BY database_id
)
SELECT
@@SERVERNAME As ServerName,
UPPER(d.name) AS DatabaseName,
(SELECT
MAX(v.ActivityDate)
FROM
(VALUES (la.last_seek), (la.last_scan), (la.last_lookup), (la.last_update)) v
(ActivityDate)) AS [LastActivityDate(Seek|Scan|Lookup|Update)],
d.State_Desc AS [State],
d.Recovery_Model_Desc AS [Recovery],
d.Is_CDC_Enabled,
d.Is_Encrypted
FROM sys.databases d
LEFT JOIN LastActivity la
ON d.database_id = la.database_id
ORDER BY d.name ASC;
|
|
|
|
|
|