January 25, 2006 at 10:26 am
How do REAL administrators clean up their dbs. How to know if a database is even being used.
We have many databases and i am sure they are not all being used. But I cannot see in EM where to know the last modification date or time it was accesses/used.
thanx!
January 25, 2006 at 11:13 am
EM isn't going to contain this information. One of the things you can do is profile activity filtered to restrict only to a database you don't believe is still in use. If you don't see anything as a result of the traces (except some test queries of your own to verify the trace is picking up properly), then that would be a good database to consider removing from the SQL Server. Worst case is you detach the database and move the files to a safe location. Should someone have a need for the database, you can quickly re-attach.
K. Brian Kelley
@kbriankelley
January 26, 2006 at 9:54 am
I prefer a less hands-on approach. First off communication to the user community. Then I'll put the databse into 'read only' mode for a few days, maybe a week. If the database is needed for update, it will usually come up pretty quickly and you can just change the option back. Now if a few days go by and no body complains about is then I'll create a new user and perform a changedbowner to that user (not mapping the existing dbo's) and then put the database into 'dbo use' only mode. This way you can catch those users and poorly written applications that have 'dbo' access (again, it's quick and easy to undo this). Again if a few days (or a week) go by with no issues my final step is to make a final backup of the database and make sure it gets to tape, then put the database in 'offline' mode. Now depending on the server and the database, I may leave it out there for 30 days or so just to avoid the unpleasant task of going through a tape restore. If no one raises a red flag, then it's time for the old 'drop database' and you're though.
All in all the steps above are probably about 15-20 minutes of work scattered over a time frame of 2 weeks to 2 months. I think it's less labor intensive than profiler and just as thorough.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply