Last “Touch” on each Database per Instance

Today i was asked if some of the databases on a specific instance were in use or not.
As this is an interesting question that could be easily answered by the SQL Server DMVs i wrote a little script to answer this question.

It creates a temporary table with five colummns named DatabaseName, TableName, IndexType, LastTouch and ActualDate (this is just for comparison).
It then executes sp_MSforeachdb and inserts a record for each database within the instance except for the system databases.
It then tells us which table was recently used and when.

/*
@cms4j 2014-01-13
Script to get the latest "touch" on each database
to get to know wheather the database is in use or not.
*/

CREATE TABLE #LastTouchOnDatabase
(
[DatabaseName] nvarchar(200) NULL
, [TableName] nvarchar(200) NULL
, [IndexType] nvarchar(200) NULL
, [LastTouch] datetime NULL
, [ActualDate] datetime NULL
)
 
EXEC dbo.sp_MSforeachdb 'USE [?];
INSERT INTO #LastTouchOnDatabase
(DatabaseName, TableName, IndexType, LastTouch, ActualDate)
SELECT TOP 1	DB_NAME() AS [Database Name]
				, o.name
				, CASE i.index_id	WHEN 0 THEN ''Heap''
									WHEN 1 THEN ''Clustered Index''
									ELSE ''NonClustered Index''
				  END AS [Index Type]
				, CASE	WHEN ISNULL(ius.last_user_update,convert(datetime,''1900-01-01'')) >= ISNULL(ius.last_user_scan,convert(datetime,''1900-01-01'')) AND ISNULL(ius.last_user_update,convert(datetime,''1900-01-01'')) >= ISNULL(ius.last_user_seek,convert(datetime,''1900-01-01'')) AND ISNULL(ius.last_user_update,convert(datetime,''1900-01-01'')) >= ISNULL(ius.last_user_lookup,convert(datetime,''1900-01-01'')) THEN ISNULL(ius.last_user_update,convert(datetime,''1900-01-01'')) 
						WHEN ISNULL(ius.last_user_scan,convert(datetime,''1900-01-01'')) >= ISNULL(ius.last_user_seek,convert(datetime,''1900-01-01'')) AND ISNULL(ius.last_user_scan,convert(datetime,''1900-01-01'')) >= ISNULL(ius.last_user_lookup,convert(datetime,''1900-01-01'')) THEN ISNULL(ius.last_user_scan,convert(datetime,''1900-01-01''))
						WHEN ISNULL(ius.last_user_seek,convert(datetime,''1900-01-01'')) >= ISNULL(ius.last_user_lookup,convert(datetime,''1900-01-01'')) THEN ISNULL(ius.last_user_seek,convert(datetime,''1900-01-01''))
						ELSE ISNULL(ius.last_user_lookup,convert(datetime,''1900-01-01''))
				  END AS [Last Touch On Table]
				, GETDATE() as [GETDATE()]
FROM			sys.indexes i
INNER JOIN		sys.objects o ON i.object_id = o.object_id
INNER JOIN		sys.partitions p ON i.object_id = p.object_id AND i.index_id = p.index_id
INNER JOIN		sys.databases sd ON sd.name = ''?''
LEFT OUTER JOIN sys.dm_db_index_usage_stats ius ON i.object_id = ius.object_id AND i.index_id = ius.index_id AND ius.database_id = sd.database_id
WHERE			o.type_desc = ''USER_TABLE''
AND				sd.database_id > 4
ORDER BY		4 DESC'
 
SELECT * FROM #LastTouchOnDatabase ORDER BY 4 DESC
 
DROP TABLE #LastTouchOnDatabase

Advertisements
This entry was posted in SQL Server and tagged . Bookmark the permalink.

One Response to Last “Touch” on each Database per Instance

  1. cms4j says:

    Reblogged this on cms4j and commented:

    Just redefined it.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s