SQL Script: - How to find the last access date of a database

,

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




Rate

Share

Share

Rate