Log Shipping – NORECOVERY vs. STANDBY mode

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.