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.

Tags: , , , , , , , , ,

This entry was posted on Monday, April 30th, 2012 at 08:22 and is filed under Technology. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.


23 Responses to “SQL Server: Identify UnUsed Databases”

  1. Mike H says:

    Best technique to determine unused sql server 2000 databases?

  2. J says:

    HI Vinod

    I need your help. We have third party application deployed on server boxes in Datarooms. The databases are also deployed on the same box. We do not have the database connections configured and hence we are at a loss to understand which application accesses which database. We used this script to eliminate the databases not being used, but can you provide some useful tip by which we can find which application uses which database(s)?

    I would appreciate if you could reply to the above query.

    Regards,

    • Vinod Kumar says:

      Jency – It is not going to be easy. But there are few workarounds that can be tried out.

      1. If you are using separate usernames for each application then we can track on LogonTrigger to who is logging into the system and then the redirection.

      2. If the above thing is not the case, then in Logon Trigger – find the default database you are connection from sys.server_principals and then log the same.

      3. You can also try to see if AUDIT_LOGON gives you any additional information. That is yet another direction to explore.

      These are some of the options that come to mind.

  3. Jency says:

    Hi

    We are working on a SQL Server upgrade for the databases in the Data Center and your posts are being very useful now

    Thanks & Regards,
    Jency

  4. Pinakin says:

    Hi Can you please tell me aboiut the SQL server 2000 box boz I am migrating the database but I want to leave that database which one is not use any more I try many script but all are that was not working I am requesting you pls help me out VINOD

  5. J says:

    the time stamp for last_user_lookup, last_user_scan, last_user_seek, and last_user_update are all current to the time that I execute your script. What am I doing wrong?

    • Vinod Kumar says:

      Jason – this will be the case because we are just finding the last time a table was accessed inside a DB. It doenst give you when the table was last accessed. Hence there is nothing wrong here. If the values are current then there is a workload on the table and hence the values are current.

  6. srinivas d says:

    Hi Vinod
    This the link for Database last accessed time -suggestion

    https://connect.microsoft.com/SQLServer/feedback/details/659846/database-last-accessed-time

  7. kin says:

    Hi,
    Nice script…

    This one is better to use as it does not use the undocumented “sp_msforeachdb” …

    SELECT db_name(d.database_id),MAX(last_user_SCAN) as last_user_SCAN,MAX(last_user_Seek) as last_user_Seek,MAX(last_user_lookup) as last_user_lookup,max(last_user_update) as last_user_update
    from sys.databases d
    left join sys.dm_db_index_usage_stats i on d.database_id=i.database_id
    group by db_name(d.database_id)

    HTH,

    Cheers !

  8. THis is the only method I am aware of, and have used by one of my peer. When we discussed the option, we have cruel words – “let’s keep databases in DBO Only mode” and see if somebody complains? :P

    • Vinod Kumar says:

      Yeah, that is one of the options used when with SQL Server 2000 to either make it Single user mode and wait for application users to shout and after one month no-one comes back. Just detach and keep the DB for couple more months before archiving the same.

  9. srinivas says:

    Thanks Vinod.So we are working with more than 4000 databases on several instances. This script would definetly help to start..reducing the unwanted db’s . This helps in reducing the cost for customers..on managing the space. However i have read in MS Connect website that they are going to come with a separte column for this.

    • Vinod Kumar says:

      Thanks for your note Srinivas. Glad to hear this script will be a starting point. Yes, there will always be features added into the SQL Server world every other release to refine and make it compelling. If you find time to post the connect request it would be great for people too :).

  10. Shashikant Shakya says:

    Thanks a lot Vinod for sharing this. :)

Leave a Reply