SQL Script to find database last access date
Sometime we create databases on Test/ Development database servers for temporary uses for short duration and forgot to delete them after the work got completed. As a part of house keeping activity, we need to find out when a database is last accessed in order to find out the databases which are not used since long time and can be a deleted.
Below mentioned SQL query will going to help you in finding out the last access date of all the databases of a SQL server
SELECT name as [Database Name], [Last Access Date] =(select MAX(temp.lastaccess)
from ( select lastaccess =
max(last_user_seek)
where max(last_user_seek)is not null
union all
select lastaccess = max(last_user_scan)
where max(last_user_scan)is not null
union all
select lastaccess = max(last_user_lookup)
where max(last_user_lookup) is not null
union all
select lastaccess =max(last_user_update)
where max(last_user_update) is not null) temp)
FROM master.dbo.sysdatabases sysdb
left outer join sys.dm_db_index_usage_stats Idxus
on sysdb.dbid= Idxus.database_id
group by sysdb.name
Share this:
Discover more from Technology with Vivek Johari
Subscribe to get the latest posts sent to your email.
Nice, useful for practical scenarios. Specially when platforms team is screaming for space on hosted environments 😊