Wednesday, September 14, 2011

When were my databases last accessed?

This script uses sys.dm_db_index_usage_stats to get timestamps for the last read and write operations for a database.
SELECT
 DB_NAME(database_id) AS DatabaseName
   ,MAX(CASE 
  WHEN ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_scan,'1900-01-01') AND ISNULL(last_user_seek,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_seek,'1900-01-01')
  WHEN ISNULL(last_user_scan,'1900-01-01') >= ISNULL(last_user_lookup,'1900-01-01') THEN ISNULL(last_user_scan,'1900-01-01')
  ELSE ISNULL(last_user_lookup,'1900-01-01')
  END) AS LastReadOperation
   ,MAX(last_user_update) AS LastWriteOperation
FROM sys.dm_db_index_usage_stats
GROUP BY 
 DB_NAME(database_id)


No comments: