June 24, 2011 at 1:09 am
Good Morning
Is It possible to monitor which db's are in use/or being used. We upgrading to SQL 2008 on a new server and i'd like to do a cleanup before going ahead with it. We have like +- 90 db's on the instance and a few are not even being used.
Please assist it would be greatly appreciated
June 24, 2011 at 3:38 pm
Take a look at the sys.databases table in master
Chris Powell
George: You're kidding.
Elroy: Nope.
George: Then lie to me and say you're kidding.
June 24, 2011 at 3:53 pm
I don't think there is a cast iron way of doing this. a database may only be used once a month or once a quarter
The DMV Sys.dm_db_index_usage_stats lists usage statistics for each index in a database, if this was 0 for all indexes then perhaps the database in not in use. there could always be some indexes not used because no query requires them.
Also sys.dm_io_virtual_file_stats lists IO per database file, so low values there suggest a low usage database. Remember your own housekeeping will cause database IO.
Other than that its run profiler (as a server side trace) but you need to run it for a long time to be sure, or set a database read_only or offline and see who screams. 🙂
also - the dmvs get refeshed with each SQL restart........
---------------------------------------------------------------------
June 24, 2011 at 4:56 pm
I like the idea of using a trace over time, but if you find databases that aren't in the default trace, I'd close them and set them offline. That way all permissions and structures are there, and if someone complains, you can bring them online.
Make sure that all help desk and support people know you're doing this so that if a call comes in when you're gone, someone can handle it.
June 24, 2011 at 11:55 pm
Wow, it must be spring, err summer, cleaning time again. I think I have seen a thread like this 3 or 4 times this week. Another good suggestion from another thread (I think it was Ninja's, but not sure) was to set AutoClose ON on the databases in question and then watch the SQL Server error logs for messages showing that the database was Opened. Once you see it is in use turn AutoClose OFF again.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 26, 2011 at 11:48 pm
Thanks A lot for all the assistance, will run SQL profiler and work forward from there, will take advice and set to offline hopefully nobody would skin me alive 😀
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply