/* Find DB activity for known list of DBs ALL CREDIT goes to: http://sqlblog.com/blogs/aaron_bertrand/archive/2008/05/06/when-was-my-database-table-last-accessed.aspx I merely tweaked for my own needs -- You can adjust the script to select from sys.databases WHERE Name in (....). But -- as for me I like to keep a second listing in a management DB CREATE TABLE [DBs] ( [UID] [int] NOT NULL IDENTITY(1, 1) NOT FOR REPLICATION , [DBID] [int] NULL , [DBName] [nvarchar](500) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Type] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateCreated] [datetime] NOT NULL CONSTRAINT [DF_DBs_DateCreated] DEFAULT (GETDATE())) ON [PRIMARY] */ DECLARE @DBName VARCHAR(128) --Database name , @SQL VARCHAR(8000) , @DBCount INT , @DBCounter INT IF OBJECT_ID('tempdb..#tmpDBs') IS NOT NULL DROP TABLE #tmpDBs IF OBJECT_ID('tempdb..##tmpDBActivity') IS NOT NULL DROP TABLE ##tmpDBActivity CREATE TABLE ##tmpDBActivity(DBName sysname,last_read DATETIME, last_write DateTime) SELECT ROW_NUMBER() OVER (ORDER BY DBName ASC) IDX , DBName INTO #tmpDBs FROM ManageDBs.dbo.DBs SELECT @DBCount = @@ROWCOUNT, @DBCounter = 1 WHILE @DBCounter <= @DBCount BEGIN SELECT @DBName = DBName FROM #tmpDBs Where IDX = @DBCounter SELECT @SQL = ' USE ' + QUOTENAME(@DBName,'[') + ' ;WITH agg AS ( SELECT last_user_seek, last_user_scan, last_user_lookup, last_user_update FROM sys.dm_db_index_usage_stats WHERE database_id = DB_ID() AND OBJECT_SCHEMA_NAME([object_id]) = ' + QUOTENAME('dbo',CHAR(39)) + ' ) INSERT INTO ##tmpDBActivity SELECT DB_NAME(DB_ID()) DBName ,last_read = MAX(last_read) ,last_write = MAX(last_write) FROM ( SELECT last_user_seek, NULL FROM agg UNION ALL SELECT last_user_scan, NULL FROM agg UNION ALL SELECT last_user_lookup, NULL FROM agg UNION ALL SELECT NULL, last_user_update FROM agg ) AS x (last_read, last_write);' EXEC (@SQL) SELECT @DBCounter = @DBCounter+1 END SELECT DBName , last_read , last_write , CASE WHEN last_read IS NULL AND last_write IS NULL THEN 'INACTIVE' ELSE '' END STATUS FROM ##tmpDBActivity ORDER BY last_read ASC DROP TABLE ##tmpDBActivity
Results:
DBName | last_read | last_write | STATUS |
---|---|---|---|
CheeseAndRiceCo | NULL | NULL | INACTIVE |
ChickenButtLLC | NULL | NULL | INACTIVE |
FranksNBeansLTD | 2011-05-11 10:48:28.197 | NULL | |
BobCo | 2011-06-09 10:46:30.923 | NULL | |
BadSeafood | 2011-07-07 12:01:21.137 | 2011-07-06 16:39:07.253 | |
ManageDBs | 2011-08-23 19:01:46.510 | 2011-08-23 19:01:46.510 |
Comments :
Post a Comment