When Database was Last accessed ?, this was the first question, whenever anyone wanted to drop / delete a database. Today I was involve in some cleaning activity on one of the development server because of some disk space issues.
Every developer has a SA rights on this development box. As a large team of 50 developers were managing this box so there were some databases which were temporarily created on the fly because of some requirements and never get dropped. Now few databases are not being owned by anyone and nobody wanted to take a ownership for deleting those.
Here is solution which I developed to figure out when a specified database was lastly used. If the database was not being used in last one month, means I am good to drop that :)
SQL Script to find out when Database was Last accessed
|
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
|
Note: – This query uses DMV which hold the information from the point where sql services were started. Do not use this query if your server is restarted recently.
Hi, thanks for sharing the scripts. It works great! Your script save me time and trouble ;).
ThQ.
Yobo
when i used the script to get the list of all the Db and the last access time, i got a lot of NULL in the output. And the ones that i got as last access date and time are only of the current month mostly last 2 days.
thanks.