Search This Blog

Identifying Unused Databases

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.

1 comment:

Alwin Co Daan said...

This is an amazing blog,it gives very helpful messages to us.Besides that Wisen has established as Best Dot Net Training in Chennai. or learn thru ASP.NET Online Training . Nowadays Dot Net has tons of job opportunities on various vertical industry.