Do you ever wonder if there are any databases in your environment that may just be there but not being used?
I needed to make a comprehensive list of such databases in order to clean up old databases and prep old/existing environment and migrate and consolidate them into a new sql server infrastructure.
So I wrote this DMV query to obtain when a database was last accessed for a user query as well as those that were left OFFLINE but never removed.
I executed this against CMS / Multi Server query an whola... within few seconds I had the complete list or rather a preliminary list for my intended purpose.
Couple things to note first:
1. I only tested and ran this against SQL versions 2008 and up.
2. It uses a global temp table and removes when done
Hope you find this use and please feel free to leave comments/feedback/suggestions for further improvement.
SET nocount ON
SET TRANSACTION isolation level READ uncommitted
USE master
go
IF Object_id('tempdb..##t_dba_db_last_access_stats') IS NOT NULL
DROP TABLE ##t_dba_db_last_access_stats
go
DECLARE @db_activity_since INT
SET @db_activity_since = 90 -- days
IF Object_id('tempdb..##t_dba_db_last_access_stats') IS NULL
CREATE TABLE ##t_dba_db_last_access_stats
(
db_name NVARCHAR(256),
db_status NVARCHAR(256),
last_user_seek DATETIME,
last_user_scan DATETIME,
last_user_lookup DATETIME,
last_user_update DATETIME
)
go
EXEC Sp_msforeachdb
' use [?] if db_id() > 4 begin insert into ##t_dba_db_last_access_stats SELECT DB_NAME() db_name, CAST(DATABASEPROPERTYEX(DB_NAME(), ''Updateability'') AS NVARCHAR(256)) db_status, last_user_seek = MAX(last_user_seek), last_user_scan = MAX(last_user_scan), last_user_lookup = MAX(last_user_lookup), last_user_update = MAX(last_user_update) FROM sys.dm_db_index_usage_stats AS i WHERE i.database_id = DB_ID() AND OBJECTPROPERTY(i.object_id, ''ismsshipped'') != 1 end '
SELECT Getdate() [current_time],
@@servername sql_instance,
(SELECT crdate FROM sysdatabases WHERE NAME = 'tempdb') sql_instance_up_since,
db_name,
db_status,
Max(last_accessed_date) last_accessed
FROM ##t_dba_db_last_access_stats a
UNPIVOT ( last_accessed_date
FOR last_accessed_for IN (last_user_seek,
last_user_scan,
last_user_lookup,
last_user_update ) ) AS last_accessed
GROUP BY db_name, db_status
UNION ALL -- Lets also grab list of OFFLINE databases
SELECT Getdate() [current_time],
@@servername sql_instance,
(SELECT crdate
FROM sysdatabases
WHERE NAME = 'tempdb') sql_instance_up_since,
FROM sysdatabases
WHERE NAME = 'tempdb') sql_instance_up_since,
NAME db_name,
Cast(Databasepropertyex(NAME, 'status') AS VARCHAR(50)) db_status,
NULL last_accessed
FROM sysdatabases
WHERE NAME NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
AND Cast(Databasepropertyex(NAME, 'status') AS VARCHAR(50)) = 'OFFLINE'
ORDER BY db_name
IF Object_id('##t_dba_db_last_access_stats') IS NOT NULL
DROP TABLE ##t_dba_db_last_access_stats
go
