How do you find the last time a database was accessed and size?
We had about 108 databases on one of our Test Servers and it was being used by multiple teams and we don’t have comprehensive list of what’s being used by who.
Some Developer may be pointing to an instance from their Dev Box.
This query helps to tentatively figure out last time the database is accessed. Its not completely accurate but still gives the ballpark numbers which would help to do the database spring cleaning.
;
WITH d AS ( SELECT d = database_id ,
name
FROM sys.databases
WHERE state = 0
AND database_id BETWEEN 5 AND 32766
),
index_usage ( d, lsk, lsc, llk, lupd )
AS ( SELECT database_id ,
MAX(last_user_seek) ,
MAX(last_user_scan) ,
MAX(last_user_lookup) ,
MAX(last_user_update)
FROM sys.dm_db_index_usage_stats
WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
),
proc_stats ( d, lproc )
AS ( SELECT database_id ,
MAX(last_execution_time)
FROM sys.dm_exec_procedure_stats
WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
),
trig_stats ( d, ltrig )
AS ( SELECT database_id ,
MAX(last_execution_time)
FROM sys.dm_exec_trigger_stats
WHERE database_id BETWEEN 5 AND 32766
GROUP BY database_id
),
query_stats ( d, lquery )
AS ( SELECT t.[dbid] ,
MAX(s.last_execution_time)
FROM sys.dm_exec_query_stats AS s
CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) AS t
WHERE t.[dbid] BETWEEN 5 AND 32766
GROUP BY t.[dbid]
)
SELECT d.name ,
i.lsk AS LastUserSeek ,
i.lsc LastUserScan ,
i.llk LastUserLookup ,
i.lupd LastUserUpdate ,
p.lproc ProcExecution ,
t.ltrig LastTrigger ,
q.lquery LastQuery
FROM d
LEFT OUTER JOIN index_usage AS i ON d.d = i.d
LEFT OUTER JOIN proc_stats AS p ON d.d = p.d
LEFT OUTER JOIN trig_stats AS t ON d.d = t.d
LEFT OUTER JOIN query_stats AS q ON d.d = q.d
ORDER BY COALESCE(i.lsk, i.lsc, i.llk, i.lupd, p.lproc, t.ltrig, q.lquery) DESC
Quicky way to Get the Database size?
SELECT sys.databases.name ,
SUM(size) * 8 / 1024 AS [Total_disk_pace_MB]
FROM sys.databases
JOIN sys.master_files ON sys.databases.database_id = sys.master_files.database_id
GROUP BY sys.databases.name
ORDER BY 2 DESC ,
sys.databases.name;