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
