6

I have a number of databases on multiple servers that don't seem to be used any longer. I'd like to drop these databases, but I'm uncertain if they are actually in use.

I can't find their owners, there are no processes linked to them, and nothing has been written to the tables for a few months now.

Login auditing was not enabled prior to today.

Servers versions range from 2005 to 2014, but most of these are on 2008 R2.

Is there a way to check the history of logins for these databases, going back 6 months, just to double check with these users, before taking the databases offline?

I got a suggestion to look at index usage history; is someone able to share a script for that?

6

I blogged about it exactly one month ago. Since link-only answers are discouraged and pasting here the whole blog post would be ridiculous, here is a summary of what you will find there.

Basically, you have to record index usage in a user table, in order to make sure that server restarts and index maintenance don't delete entries for the databases you're interested in.

A second method involves auditing index usage by means of streaming an Extended Events session that captures lock acquired events. This method is particularly useful for capturing additional information about who is accessing the tables without having to capture detail information (audits and XE file targets would be too verbose for this task).

I hope you find it useful.

2

Got a suggestion on looking at index usage history, anyone happy to share a script please

Below Script would get you the index usage history:

From below, we can get an idea of how many seeks, scans, lookups and overall updates (insert, update and delete) occurred

   DECLARE @dbid INT
--To get Datbase ID
SET @dbid = Db_id( )

SELECT
 Db_name( d.database_id )    database_name
 ,Object_name( d.object_id ) object_name
 ,s.name                     index_name
 ,c.index_columns
 ,d.*
FROM
 sys.dm_db_index_usage_stats d
 INNER JOIN sys.indexes s
  ON d.object_id=s.object_id
     AND d.index_id=s.index_id
 LEFT OUTER JOIN ( SELECT DISTINCT
                    object_id
                    ,index_id
                    ,Stuff( ( SELECT
                               ','+Col_name( object_id, column_id ) AS 'data()'
                              FROM
                               sys.index_columns t2
                              WHERE  t1.object_id=t2.object_id
                                 AND t1.index_id=t2.index_id
                              FOR XML PATH ('') ), 1, 1, '' ) AS 'index_columns'
                   FROM
                    sys.index_columns t1 ) c
  ON c.index_id=s.index_id
     AND c.object_id=s.object_id
WHERE  database_id=@dbid
   AND Objectproperty( d.object_id, 'IsIndexable' )=1
ORDER  BY
 index_columns
 ,object_name
 ,( user_seeks+user_scans+user_lookups+system_seeks+system_scans+system_lookups ) DESC

-- To find index scans
SELECT
 Db_name( d.database_id )    database_name
 ,Object_name( d.object_id ) object_name
 ,s.name                     index_name
 ,c.index_columns
 ,d.*
FROM
 sys.dm_db_index_usage_stats d
 INNER JOIN sys.indexes s
  ON d.object_id=s.object_id
     AND d.index_id=s.index_id
 LEFT OUTER JOIN ( SELECT DISTINCT
                    object_id
                    ,index_id
                    ,Stuff( ( SELECT
                               ','+Col_name( object_id, column_id ) AS 'data()'
                              FROM
                               sys.index_columns t2
                              WHERE  t1.object_id=t2.object_id
                                 AND t1.index_id=t2.index_id
                              FOR XML PATH ('') ), 1, 1, '' ) AS 'index_columns'
                   FROM
                    sys.index_columns t1 ) c
  ON c.index_id=s.index_id
     AND c.object_id=s.object_id
WHERE  database_id=@dbid
       AND Objectproperty( d.object_id, 'IsIndexable' )=1
   AND ( d.user_scans+d.system_scans )>0
ORDER  BY
 d.user_scans+d.system_scans

--To find unused indexes
-- This query will give you details of unused indexes with details like object_name index_name,index_type , index columns .
-- We are considering only indexes so we are omitting index_type heap, clustered since it represents tables
SELECT
 Object_name( i.object_id ) object_name
 ,i.name                    index_name
 ,i.index_id                index_id
 ,i.type_desc               type_desc
 ,c.index_columns
FROM
 sys.indexes i
 LEFT OUTER JOIN sys.dm_db_index_usage_stats d
  ON d.object_id=i.object_id
     AND i.index_id=d.index_id
     AND d.database_id=@dbid
 LEFT OUTER JOIN ( SELECT DISTINCT
                    object_id
                    ,index_id
                    ,Stuff( ( SELECT
                               ','+Col_name( object_id, column_id ) AS 'data()'
                              FROM
                               sys.index_columns t2
                              WHERE  t1.object_id=t2.object_id
                                 AND t1.index_id=t2.index_id
                              FOR XML PATH ('') ), 1, 1, '' ) AS 'index_columns'
                   FROM
                    sys.index_columns t1 ) c
  ON c.index_id=i.index_id
     AND c.object_id=i.object_id
WHERE  Objectproperty( i.object_id, 'IsIndexable' )=1
       AND d.index_id IS NULL
   AND i.type_desc NOT IN ( 'heap', 'clustered' )
-- To find only indexes which are only updated but not used in index seek, lookup or scan.
SELECT
 Object_name( i.object_id ) object_name
 ,i.name                    index_name
 ,i.index_id                index_id
 ,i.type_desc               type_desc
 ,c.index_columns
 ,d.user_updates
 ,d.user_seeks
 ,d.user_scans
 ,d.user_lookups
 ,d.system_updates
 ,d.system_seeks
 ,d.system_scans
 ,d.system_lookups
FROM
 sys.indexes i
 INNER JOIN sys.dm_db_index_usage_stats d
  ON d.object_id=i.object_id
     AND i.index_id=d.index_id
     AND d.database_id=@dbid
 LEFT OUTER JOIN ( SELECT DISTINCT
                    object_id
                    ,index_id
                    ,Stuff( ( SELECT
                               ','+Col_name( object_id, column_id ) AS 'data()'
                              FROM
                               sys.index_columns t2
                              WHERE  t1.object_id=t2.object_id
                                 AND t1.index_id=t2.index_id
                              FOR XML PATH ('') ), 1, 1, '' ) AS 'index_columns'
                   FROM
                    sys.index_columns t1 ) c
  ON c.index_id=i.index_id
     AND c.object_id=i.object_id
WHERE  Objectproperty( i.object_id, 'IsIndexable' )=1
       AND i.type_desc NOT IN ( 'heap', 'clustered' )
       AND ( d.user_updates>0
              OR d.system_updates>0 )
       AND d.user_seeks=0
       AND d.user_scans=0
       AND d.user_lookups=0
       AND d.system_seeks=0
       AND d.system_scans=0
   AND d.system_lookups=0 

Note* The values for these counters get reset each time you restart SQL Server. In addition, the values for the data returned by sys.dm_db_index_operational_stats exists only as long as the metadata cache object that represents the heap or index is available.

Also, what I personally prefer for above situations is to use Sp_BlitzIndex to analyse the index usage information. Please go through the link mentioned as it simplifies problem to a great extent.

Is there a way to check the history of logins for these databases, going back 6 months, just to double check with these users, before taking the databases offline

To check for your databases not in use. Please check this excellent article from Aaron which helped me a lot along with 4 Lightweight Ways to Tell if a Database is Used

  • 2
    The OP doesn't seem to be interested in index usage per se. They want the history of logins and they've only mentioned sys.dm_db_index_operational_stats because someone suggested that DMV as a way to get the information they want. – Andriy M May 26 '15 at 16:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.