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:
- Auditing is one of the most precise and obviously a deterministic solution.
- Can even use Logon-triggers (but remember the user can switch context of the database hence will be difficult).
- 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.
TechEd India 2012
The topic of time management is something I have talked a number of times over this blog. So we are not going to spend time rehashing the same. But I still was so motivated to write this blog because I thought of picking some of the tips and traits that everyone out there does and will be a great learning opportunity for me to share.