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 =
where max(last_user_seek)is not null
select xx = max(last_user_scan)
where max(last_user_scan)is not null
select xx = max(last_user_lookup)
where max(last_user_lookup) is not null
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.