November 22, 2012 at 7:32 pm
Hi Guys,
Is there a way to identify old databases that are not in use? 😀
Well aside from checking the physical mdf file itself. is there any other way?
thanks
===============================================================
"lets do amazing" our company motto..
November 22, 2012 at 9:10 pm
Take it offline and see who complains. For safety sake, be sure to have a good backup of it as well.
November 22, 2012 at 9:15 pm
No out of the box solution is there.
But you can query system catalogue for modified date, to see schema updates.
You can run a server side trace filtered by the dbname and monitor for a period.
Finally yes may be take the database offline and be ready for surprises
November 22, 2012 at 9:53 pm
Heh... you could always do the SOFH thing... set it to offline and wait for the phone to ring. I Dev ad Staging boxes which have about 10 times as many databases as production does, I actually do use that method.
I also set databases offline for a month or two prior to deleting them once I've determined that they're not being used through other methods.
Speaking of those other methods, you can use the following with some caveats...
SELECT DatabaseName = db.name,
LastUserAction = MAX(ca.LastUserAction)
FROM sys.dm_db_index_usage_stats st
RIGHT OUTER JOIN sys.databases db ON st.database_id = db.database_id
CROSS APPLY (SELECT st.last_user_seek UNION ALL
SELECT st.last_user_scan UNION ALL
SELECT st.last_user_lookup UNION ALL
SELECT st.last_user_update
)ca (LastUserAction)
GROUP BY db.name
;
What are the caveats?
The first is that the data is only good since the last server start. If that was several months ago, then no problem. If it was yesterday, you might have a problem if a given database has very sporatic usage. For example, if it's only used once a week and you restarted the server yesterday, it might not be the correct day of the week for the DB to have been used yet. If you delete the database, I'm pretty sure you'll get a nasty email asking what happened.
The other caveat is that even just expanding tables in the Object Explorer will cause a "usage". It's pretty sensitive. To me, that doesn't constitute a "usage".
Except for archive databases, databases aren't being used if they're not ever written to. That means that something in the database is "updated" either by an Insert, Delete, or Update. That information is tracked by the last_user_update column of the dm_db_index_usage_stats system view. With that thought in mind, you might think that the following will give you a better indication if a database is being "used".
SELECT DatabaseName = db.name,
LastUserAction = MAX(ca.LastUserAction)
FROM sys.dm_db_index_usage_stats st
RIGHT OUTER JOIN sys.databases db ON st.database_id = db.database_id
CROSS APPLY ( --SELECT st.last_user_seek UNION ALL
--SELECT st.last_user_scan UNION ALL
--SELECT st.last_user_lookup UNION ALL
SELECT st.last_user_update
)ca (LastUserAction)
GROUP BY db.name
;
Ah... but the you remember that you have a couple of databases that are almost never made to suffer any data inserts or modifications that you're using as "configuration" database (We have several for a product called "Web Methods") or, perhaps, you have a "DBA" database with all of your precious DBA stored procedures that is only updated every couple of months.
So that brings us full circle back to the method I'm sure that everyone thought I was joking about when I first mentioned it. You know what the archive databases are so you add those to a list of exclusions. You do the same with any mostly-read-only configuration databases. Then you run the code above to find likely unused databases and send out an email to the world asking people if they using the databases you identified and notifying them that they will be taken offline in a week or two. Send out the same email a day or two before you take the databases offline. When the day comes, check the job history log for any failures, note them all, take a "final" backup of the databases that no one has claimed, and then take the databases that haven't been claimed offline.
Do NOT drop the databases for at least a month so that databases used only for month end processing have a chance to be claimed. Two days before you drop any of the offline databases, send out another email announcing the databases that you're going to drop. On "D" day (drop day), send out a final email at the beginning of the day with a list of databases that haven't yet been claimed. Any that remain unclaimed at the end of the day can the be dropped. Keep the backups on disk for at least another month to save having to restore from tape instead of disk because there will like be something that someone wants restored for some reason.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 22, 2012 at 9:59 pm
p.s. In 2008 and above, you can setup an audit but I believe that will still record the database as being "accessed" even if you just expand the tables using the object explorer. You might be able to do an additional join to sys.objects to eliminate any MS provided objects such as sys.objects to prevent such false returns.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2012 at 9:32 am
I do what above members have noted in looking at the index updates but add a server side trace for logins as well
November 27, 2012 at 12:21 pm
Use SQL Profiler. Setup dbname with the specific db you don't think is being used and let it run for 30 days. Check it every day or every other day to see what it captures. If it is being used you will see by what ID, what time and what SQL statements have been run. I have done this on several and have found surprises.
November 27, 2012 at 12:25 pm
you could also set the database to autoclose, and then check the sql server log to see if it was ever opened after you change the flag.
you'd have to make sure things like indexing or other maintenance functions didn't open it for you when you didn't expect it to.
you personally running sp_msforEachDB would of course open the database, for example.
Lowell
November 27, 2012 at 12:27 pm
Lowell (11/27/2012)
you could also set the database to autoclose, and then check the sql server log to see if it was ever opened after you change the flag.you'd have to make sure things like indexing or other maintenance functions didn't open it for you when you didn't expect it to.
you personally running sp_msforEachDB would of course open the database, for example.
You'd also have to exclud db backups for it as well. Problem is though you will know if something does access it but then the question remains... well... what is? Running Profiler will tell you.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply