We help IT Professionals succeed at work.

How to identify inactive SQL Databases

I require a SQL Script or some other method for finding out which databases in a SQL Server are the "least" active (or completley dormant). I.e. my problem is that I have a number of databases left over after a few SharePoint installs (which are now long gone) all with cryptic serialized database names. I don't want to delete the wrong databases for those that are still active.
Comment
Watch Question

C# ASP.NET Developer
Top Expert 2010
Commented:
Maybe this will get you going in the right direction...


SELECT
  MAX(last_user_seek),
  MAX(last_user_scan),
  MAX(last_user_lookup),
  MAX(last_user_update)
FROM sys.dm_db_index_usage_stats

Open in new window

You could try to find that out using a phased approach. First off create a list of suspected dormant DBs, then for each of the items in your list:
Stage 1 - Find out if there is any meaningful activity going on by looking at the Activity Monitor and/or Profiler. Also have a look at the SQL Server and windows event logs to see if there are any signs of non-trivial events. You should do it at least for a week in case some DBs are less occasionally used. If nothing significant there, go to the next stage.
Stage 2 - At this stage change the state of the suspected dormant DBs to ReadOnly. Give it a few days and watch out for any possible anomalies, such as someone crying in pain at their desk first thing in the morning, or some service, scheduled job or the odd app stops working altogether or with partial loss of functionality. Also keep a closer eye on SQL Server and Windows event logs. Give it at least two weeks before moving to the next stage.
Stage 3 - If there's still no sign of disaster, then you can go ahead and take the suspected DB's off line, but leave them on the server. Again like previous stage keep your eyes and ears open and don't forget to scan all logs for early signs of problem. If all clear, give this another two to three weeks before moving to the next stage.
Stage 4 - If after a few weeks there's still no one crying in pain or no sign of complaints, bring the suspected DBs online, take a backup, compress and copy to somewhere safe on the network and/or some other media such as tape or DVD and drop the DBs.

NB - From stage 2 onward, it'd make sense to have an up-to-date version of your CV handy, in case you need to start sending it out right away!  ; )

Author

Commented:
LOL, thanks.  CV is always up to date.  I have all my ducks in a row and these extra DB's could be manually identified but I like the script idea first (after testing), at least as a "glance" of what is going on.

Thanks folks.