Identifying Unused Databases

Today we looked at an old server we suspected was no longer used.
It actually had multiple SQL Instances, was poorly patched and was sitting on SQL 2005, but that isn't the point of this post. With zero documentation and a large company, we couldn't be sure this wasn't in use.

Whilst simply looking at the date & time stamps of the .MDF and .LDF files could help in some cases, maintenance jobs had been left running on this server so the smallest change in statistics meant the file dates were recent. Similarly, jobs populating the Reporting Services database were still being run, meaning data old data was being repeatedly loaded.

To evaluate whether it was worth keeping the databases online, I had some investigation to do.

Step 1 : Who is Connected?

The following TSQL summarises who is connected. It ignores the currently connected user (@@SPID) and system processes (spid's up to 50)

SELECT  loginame , nt_username, COUNT(*) AS Connections
FROM sys.sysprocesses
WHERE spid > 50 and spid != @@SPID
GROUP BY  loginame , nt_username
ORDER BY COUNT(*) DESC

Step 2 : Is there any data there?

Although not related to recent use, this gives an idea of size, historic and the table names might help decide if it is worth keeping. You never know, it might even be empty!

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SELECT 
 DB_NAME(DB_ID()) AS [Database]
 , s.name AS [Schema]
    , o.name AS [Table]
    , i.name AS [Index]
    , p.partition_number
    , p.rows AS [Row Count]
    , i.type_desc AS [Index Type]
FROM sys.partitions p
INNER JOIN sys.objects o ON o.object_id = p.object_id
INNER JOIN sys.schemas s on s.schema_id = o.schema_id
INNER JOIN sys.indexes i ON p.object_id = i.object_id
                         AND p.index_id = i.index_id
WHERE s.name != 'sys'
ORDER BY [Schema], [Table], [Index]

Step 3 : When were tables used?

Table usage can be gathered from the index usage statistics. This query shows the usage for all user tables in the current database. It excludes the system tables.

SELECT i.[database_id],
    DB_NAME(i.[database_id]) AS [Database]
 , s.name AS [SchemaName]
 , o.name AS [TableName]
 , MAX(i.[last_user_lookup]) AS [last_user_lookup]
 , MAX(i.[last_user_scan]) AS [last_user_scan]
 , MAX(i.[last_user_seek]) AS [last_user_seek]
FROM sys.dm_db_index_usage_stats AS i
INNER JOIN sys.objects o ON o.object_id = i.object_id
INNER JOIN sys.schemas s ON o.schema_id = s.schema_id
WHERE i.[database_id] = DB_ID()
  AND s.name <> 'sys'
GROUP BY i.[database_id], s.name, o.name
ORDER BY i.[database_id], s.name, o.name