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.
ALTER DATABASE
MissionCriticalProductionDB
SET
EMERGENCY;
GO
sg 8939, Level 16, State 98, Line 6
Table error: Object ID 1205579333, index ID 0, partition ID 72057594043105280, alloc unit ID 72057594049527808 (type In-row data), page (1:323). Test (IS_OFF (BUF_IOERR, pBUF->bstat)) failed. Values are 133129 and -4.
Msg 8928, Level 16, State 1, Line 6
Object ID 1205579333, index ID 0, partition ID 72057594043105280, alloc unit ID 72057594049527808 (type In-row data): Page (1:323) could not be processed. See other errors for details.
CHECKDB found 0 allocation errors and 2 consistency errors in table 'TableWithCorruption' (object ID 1205579333).
CHECKDB found 0 allocation errors and 2 consistency errors in database 'MissionCriticalProductionDB'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (MissionCriticalProductionDB).
Good luck!
This blog was initially published at our Linkedin blog