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