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

DBA

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

55 thoughts on “SQL Script: – How to find the last access date of a database

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

  2. Pingback: Extra resources
  3. Pingback: didi referral code
  4. Pingback: 카지노사이트
  5. Pingback: movies
  6. Pingback: movies online
  7. Pingback: karan johar
  8. Pingback: Top Movies
  9. Pingback: Movies1
  10. Pingback: 11 10 2019
  11. Pingback: Serial smotret
  12. Pingback: kinokrad
  13. Pingback: kinokrad 2020
  14. Pingback: nha cai fun88
  15. Pingback: casino
  16. Pingback: filmy-kinokrad
  17. Pingback: kinokrad-2019
  18. Pingback: serial
  19. Pingback: cerialest.ru
  20. Pingback: dorama hdrezka
  21. Pingback: HDrezka
  22. Pingback: kinosmotretonline
  23. Pingback: LostFilm HD 720
  24. Pingback: bofilm
  25. Pingback: 1 seriya
  26. Pingback: topedstoreusa.com
  27. Pingback: hqcialismht.com
  28. Pingback: myonlinebuy.us
  29. Pingback: 4serial.com
  30. Pingback: See-Season-1
  31. Pingback: Evil-Season-1
  32. Pingback: Evil-Season-2
  33. Pingback: Evil-Season-3
  34. Pingback: Evil-Season-4
  35. Pingback: Dollface-Season-1
  36. Pingback: 2020
  37. Pingback: koma 2020

Leave a Reply