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

Posted on March 29, 2018 by vivekjohari

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

About vivekjohari

Database Consultant with more than 11.5 years of experience in database designing & programming and DBA related activities.  Had good experience on different databases like SQL Server, MySQL & Oracle, Azure SQL &  Big Data.
This entry was posted in DBA and tagged , . Bookmark the permalink.

35 Responses to SQL Script: – How to find the last access date of a database

  1. admin says:
    March 30, 2018 at 3:32 am

    Nice, useful for practical scenarios. Specially when platforms team is screaming for space on hosted environments 😊

    Reply

  2. Pingback: seo backlinking service

  3. Pingback: Extra resources

  4. Pingback: https://www.wasseem.org/

  5. Pingback: คาสิโนออนไลน์

  6. Pingback: didi referral code

  7. Pingback: ไวอากร้า

  8. Pingback: Creative Agency in Orlando

  9. Pingback: ธนาธร จึงรุ่งเรืองกิจ

  10. Pingback: 카지노사이트

  11. Pingback: movies

  12. Pingback: Car Crashes Compilation Russia/USA/Europe 2019 HD

  13. Pingback: movies online

  14. Pingback: karan johar

  15. Pingback: Top Movies

  16. Pingback: Movies1

  17. Pingback: 11 10 2019

  18. Pingback: smotretonline2015.ru

  19. Pingback: Serial smotret

  20. Pingback: kinokrad

  21. Pingback: kinokrad 2020

  22. Pingback: ñïèñîê íîâûõ ôèëüìîâ

  23. Pingback: nha cai fun88

  24. Pingback: Watch Tv Series Online

  25. Pingback: casino

  26. Pingback: Kinokrad 2019 Kinokrad Hd

  27. Pingback: filmy-kinokrad

  28. Pingback: kinokrad-2019

  29. Pingback: filmy-2019-kinokrad

  30. Pingback: serial

  31. Pingback: cerialest.ru

  32. Pingback: cornhole board dimensions

  33. Pingback: dorama hdrezka

  34. Pingback: HDrezka

  35. Pingback: kinosmotretonline

Leave a Reply