Which Databases are Active in a SQL Server Instance?

Sometimes I see SQL Server instances with hundreds or even thousands of databases. This is usually a consolidated environment or a hosting environment or a multi-tenant application with one database per account.

Whatever the reason is, at some point you might need to figure out which databases are actually active databases and which are not. When I say "Active Databases", I mean databases that users have access recently. The meaning of "recently" can also be tricky, because in one environment, if users haven't accessed a database in the past 10 minutes, it might be considered inactive, while in another environment 10 minutes of inactivity might be normal. So it's up to you to define "recently" for your environment.

Why would you need to know which databases are active and which aren't?

One reason is that you are running out of disk space, and you need to do some cleanup. If a database hasn't been accessed for over a month, then maybe it makes sense to drop it. Or at least take a backup, store it in another location, and then drop the inactive database. Another reason is that you are about the migrate all the databases to a new server, and you don't want to waste time on inactive databases that you don't need in the new server.

So how can you find the last time a user accessed a database?

There are several options to achieve this, and I will show you one of them. This option uses the sys.dm_db_index_usage_stats dynamic management view, which stores statistics about the usage of each index in the instance. Among other things, it stores the last time the index has been used by a user for each type of index operation (seek, scan, lookup and update). Now, if users access a database, then they run queries against it, and if they run queries, then they access indexes. So if we calculate the date & time of the last index operation for any index and any operation type in a given database, we will get the last time a user accessed the database.

The following query retrieves the last user access date & time for every user database in the current instance. Notice that the sys.dm_db_index_usage_stats view is cleared whenever the instance is restarted, so make sure you run this query after some time since the last restart.

SELECT
	DatabaseName		= DB_NAME (UserDatabases.database_id) ,
	LastAccessDateTime	= MAX (LastOperations.LastAccessDateTime)
FROM
	sys.databases AS UserDatabases
LEFT OUTER JOIN
	sys.dm_db_index_usage_stats AS IndexUsageStats
ON
	UserDatabases.database_id = IndexUsageStats.database_id
CROSS APPLY
	(
		VALUES
			(IndexUsageStats.last_user_lookup) ,
			(IndexUsageStats.last_user_scan) ,
			(IndexUsageStats.last_user_seek) ,
			(IndexUsageStats.last_user_update)
	)
	AS
		LastOperations (LastAccessDateTime)
WHERE
	UserDatabases.database_id > 4
GROUP BY
	UserDatabases.database_id
ORDER BY
	LastAccessDateTime DESC;
GO

Good luck!

---

This blog was initially published at our Linkedin blog