How to monitor history of database usage if login auditing is not enabled?

Question

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?

Answer 1

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.

Answer 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

• Andriy M: 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.