Are my databases being used?

I came across a SQL 2005 server recently where it's administrator had forgotten which databases were in use (among other things). With the goal of wanting to perform a tidy up on the server, I wrote this script to prevent a lenghty, painful manual process.

The script fetches data about when the databases were last used and returns the following

• DatabaseName (Obvious really) 
• LastReadOperation - aggregated from index information in sys.dm_db_index_usage_stats
• LastWriteOperation - aggregated from index information in sys.dm_db_index_usage_stats
• DataFileDateStamp - file system date stamp 
• LogFileDateStamp - file system date stamp 

It is based on 'When were my databases last accessed ?' and fetches the file date information from the file system by using dbo.get_file_date. To fetch the file system information, it does use xp_cmdshell , which may be a security concern depending on your environment. Becauase it uses the file system, you need to run it in Management Studio on the server you are auditing.

USE master
GO

exec sp_configure 'show advanced options', 1;
reconfigure;
exec sp_configure 'xp_cmdshell',1;
reconfigure;
GO
IF OBJECT_ID('tempdb..##Results') IS NOT NULL
 BEGIN
 DROP TABLE ##Results
 END

CREATE TABLE ##Results ([DatabaseName] sysname NULL, [FileName] sysname NULL, [PhysicalName] NVARCHAR(260) NULL,  
    [FileType] VARCHAR(4) NULL, [DateFileSystem] DATETIME ) 
DECLARE @SQL VARCHAR(MAX)
 SELECT @SQL =  
 'USE [?] INSERT INTO ##Results([DatabaseName], [FileName], [PhysicalName],  
 [FileType])  
 SELECT DB_NAME(), 
 [name] AS [FileName],  
 physical_name AS [PhysicalName],  
 [FileType] =  
 CASE type 
 WHEN 0 THEN ''Data'''  
 +  'WHEN 1 THEN ''Log''' 
 +  'END
 FROM sys.database_files  (NOLOCK)
 ORDER BY [FileType], [file_id]' 
 
EXEC sp_MSforeachdb @SQL 
 
DECLARE @file_date_op datetime 

DECLARE  @db SYSNAME
DECLARE @filename NVARCHAR(260)
DECLARE tablecursor CURSOR FORWARD_ONLY FOR 
SELECT databasename, physicalname 
FROM   ##results

OPEN tablecursor 

WHILE (1 = 1) 
BEGIN 
FETCH NEXT FROM tablecursor 
INTO @db,@filename 

IF @@FETCH_STATUS <> 0 
BREAK; 
SET @file_date_op = null
print @filename
exec master.dbo.get_file_date 
  @file_name = @filename
 ,@file_date = @file_date_op OUTPUT
UPDATE ##Results 
SET  DateFileSystem = @file_date_op
WHERE   physicalname = @filename 
END 
CLOSE tablecursor 
DEALLOCATE tablecursor 

SELECT
 Name AS DatabaseName
   ,REPLACE(CONVERT(VARCHAR(30),MAX(CASE 
  WHEN ISNULL(last_user_seek,0) >= ISNULL(last_user_scan,0) AND ISNULL(last_user_seek,0) >= ISNULL(last_user_lookup,0) THEN ISNULL(last_user_seek,0)
  WHEN ISNULL(last_user_scan,0) >= ISNULL(last_user_lookup,0) THEN ISNULL(last_user_scan,0)
  ELSE ISNULL(last_user_lookup,0)
  END),121),'1900-01-01 00:00:00.000','') AS LastReadOperation
   ,REPLACE(CONVERT(VARCHAR(30),ISNULL(MAX(last_user_update),0),121),'1900-01-01 00:00:00.000','')  AS LastWriteOperation
   ,DataFile.DateFileSystem AS DataFileDateStamp
   ,LogFile.DateFileSystem AS LogFileDateStamp
FROM sys.databases d
LEFT JOIN sys.dm_db_index_usage_stats s
ON s.database_id = d.database_id 
LEFT JOIN ##Results DataFile
ON DataFile.DatabaseName = DB_NAME(d.database_id)
LEFT JOIN ##Results LogFile
ON LogFile.DatabaseName = DB_NAME(d.database_id)
WHERE DataFile.FileType = 'Data'
  AND LogFile.FileType = 'Log'
GROUP BY 
 d.name
   ,DataFile.DateFileSystem
   ,LogFile.DateFileSystem

GO