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
