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?
Best Answer
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.