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.