Previous Next

Tips and Tricks: Identify Inactive Databases by Checking Statistics

Star InactiveStar InactiveStar InactiveStar InactiveStar Inactive
 

Recently, after assessing a client’s backup statistics, we discovered that over 20% of a client’s database instances had not been backed up.  Before configuring backups for so many instances, we wanted to discover just how many databases were actually actively in use.

After investigating options, we developed the following query to detect database inactivity.  The query uses the command sys.dm_db_index_usage_stats.   The query returns the last accessed time for all indexes (including heap). Based on this, we are able to determine the last accessed time for each database.  Any databases with a last access data beyond an agreed upon threshold (e.g. older than 1 week) can be flagged for follow up.

One Drawback:  If the SQL Server service is restarted, last accessed stats are reset. So, this query is not useful for detecting database activity for servers that are frequently restarted. However, the query also returns the server restart date, which you can use to help you flag those restarts for further analysis.

SELECT DISTINCT
       server_restarted =(SELECT create_date 
                           FROM   sys.databases 
                           WHERE name=‘tempdb’)
     , 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)
     , last_seek = max(last_user_seek)
     , last_scan = max(last_user_scan)
     , last_lookup = max(last_user_lookup)
     , last_update = max(last_user_update)
FROM master.dbo.sysdatabases sd
left
join sys.dm_db_index_usage_stats s
on   sd.dbid= s.database_id
group by sd.name
having   sd.name not in(‘master’,‘tempdb’,‘model’,‘msdb’)
order by 1