SQL Script to find out when Database was Last accessed

Leave a comment (2) Go to comments

When Database was Last accessed ?, this was the first question, whenever anyone wanted to drop / delete a database. Today I was involve in some cleaning activity on one of the development server because of some disk space issues.

Every developer has a  SA  rights on this development box. As a large team of 50 developers were managing this box so there were some databases which were temporarily created on the fly because of some requirements and never get dropped. Now few databases are not being owned by anyone and nobody wanted to take a ownership for deleting those.

Here is solution which I developed to figure out when a specified database was lastly used. If the database was not being used in last one month, means I am good to drop that :)

SQL Script to find out when Database was Last accessed

SELECT name, last_access =(


select X1= max(LA.xx)

from ( select xx =

max(last_user_seek)

where max(last_user_seek)is not null

union all

select xx = max(last_user_scan)

where max(last_user_scan)is not null

union all

select xx = max(last_user_lookup)

where max(last_user_lookup) is not null

union all

select xx =max(last_user_update)

where max(last_user_update) is not null) LA)

FROM master.dbo.sysdatabases sd

left outer join sys.dm_db_index_usage_stats s

on sd.dbid= s.database_id

group by sd.name

Note: – This query uses DMV which hold the information from the point where sql services were started. Do not use this query if your server is restarted recently.

EOF - SQL Script to find out when Database was Last accessed, SQL Server 2012 , 2008 R2 , 2008 , 2005 , 2000
Leave a comment

2 Comments.

  1. Hi, thanks for sharing the scripts. It works great! Your script save me time and trouble ;).

    ThQ.

    Yobo

  2. when i used the script to get the list of all the Db and the last access time, i got a lot of NULL in the output. And the ones that i got as last access date and time are only of the current month mostly last 2 days.

    thanks.

Leave a Reply

Your email address will not be published. Required fields are marked *


*


You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>

Notify me of followup comments via e-mail. You can also subscribe without commenting.