This is one of the issues the consultant faces, when there client has little documentation on the systems and the databases they use. The first step in this process is simply looking at the date & time stamps of the .MDF and .LDF files, which could help in some cases,To evaluate whether it was worth keeping the databases online, I had some investigation to do.
Step 1 : Who is Connected?
The following TSQL summarizessummarises 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
Step2 : Check data
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!
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
Step3 : When were tables last accessed
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
Step4 : Check the latest date values on the database
Assuming that there is a datatime column and is populated periodically, this could give a better picture whether the data has been altered recently.
DECLARE @cols TABLE
(
r INT IDENTITY ,
TableName VARCHAR(100) ,
ColumnName VARCHAR(100)
);
DECLARE @i INT ,
@nSQL NVARCHAR(1000);
DECLARE @MaxData TABLE
(
TableName VARCHAR(100) ,
MaxDate DATETIME2
);
INSERT INTO @cols
SELECT TABLE_NAME ,
COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE DATA_TYPE IN ( 'Date', 'datetime', 'datetime2' )
AND OBJECTPROPERTY(OBJECT_ID(TABLE_NAME), 'IsSystemTable')=0;
SELECT @i=@@ROWCOUNT;
WHILE @i>0
BEGIN
SELECT @nSQL='SELECT '+''''+TableName+''''+', MAX('+ColumnName
+') FROM '+TableName ,
@i -= 1
FROM @cols
WHERE r=@i;
INSERT INTO @MaxData
( TableName, MaxDate )
EXECUTE ( @nSQL
);
END;
SELECT *
FROM @MaxData
ORDER BY 2 DESC;
Step5 : Audit / run Trace
Audit the database logins associated with the database, or run a trace on that database ; Some applications, access the database on some specific times like year end, end financial period etc. I wont suggest you go and delete the database if all the above steps intended towards deletion. You could take the database offline for few months before you delete it.
