«
»

Finding Used Databases

It can be difficult to determine which databases you are administering are actually being used. Below is a query that determines the last time an index was used since the last time SQL Server started to help narrow down these databases:

 
SELECT db.name, i.name, S.*, i.*
 FROM sys.dm_db_Index_Usage_Stats S
      INNER JOIN sys.DATABASES db ON db.database_id = s.database_id
      INNER JOIN sys.Indexes I
              ON S.Index_Id = I.Index_Id
              AND S.Object_Id = I.Object_Id
              AND S.DataBase_Id = DB_ID()
WHERE I.Object_Id > 1000
ORDER BY db.name

This entry was posted on Thursday, January 31st, 2008 at 9:56 pm and is filed under SQL Server Administration. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

Leave a Reply