Archive for April 30th, 2012

SQL Server: Identify UnUsed Databases April 30th, 2012

Vinod Kumar

too-many

As part of my day job, I solve lot of interesting customer problems and recently one of our customers had an interesting question to stump me. It got me really thinking hard and I wanted to share some of the solutions I shared with them. Though these solutions were given, want to also see if you have a better way to solve this question.

The Enterprise I was talking to was on more than 70+ SQL Server server installations and had close to 500+ databases running in their datacenters for various businesses. The CIO asked the datacenter administrator how many of these databases are actually used by the business and how many were stale and not used but just there because of legacy reasons. And as a database expert in the room the datacenter Administrator just turned to me and asked – “Why don’t you answer this Vinod?”

Simple solution

For every complex problems there always is a simple solution in hand. I just thought over and gave them a method almost that was convincing enough for the team. My suggestion was simple, just query the index_usage_stats DMV for some stats and then use the same to decide if the database was used or not. I just gave a simple script to the DBA like below:

Sp_msforeachdb 

‘Select @@Servername As Servername, ”?” AS DBName,

IsSystemDatabase,     

MAX(last_user_lookup) last_user_lookup,      

MAX(last_user_scan) last_user_scan,      

MAX(last_user_seek) last_user_seek,      

MAX(last_user_update) last_user_update

from [?].sys.dm_db_index_usage_stats

INNER JOIN  (      

SELECT dtb.name,

CAST(case

when dtb.name in (”master”,”model”,”msdb”,”tempdb”)

then 1

else dtb.is_distributor end AS bit) IsSystemDatabase      

FROM master.sys.databases AS dtb ) systemdatabases

ON systemdatabases.name = ”?”

GROUP BY IsSystemDatabase’ 

At least this is giving you a great starting point for discussion with the stakeholders that the database has not been accessed for the past 5+ months and if there is still business justification. The Administrator did call me back after a month that this script (simple yet powerful) was really useful. Thought to share with you all today.

PS: Remember, the statistics values can get reset if SQL Server is restarted hence this is not 100% foolproof implementation though.

What are the other options?

There are obviously other solutions that can be tried out:

  1. Auditing is one of the most precise and obviously a deterministic solution.
  2. Can even use Logon-triggers (but remember the user can switch context of the database hence will be difficult).
  3. The above Usage_Stats is great for databases of version higher than SQL Server 2005+. BTW, are you still running SQL Server 2000 versions?

Tell me your story and options to answer such situations. Would love to learn from you too.

Continue reading...