June 8, 2007 at 12:19 pm
I was wondering if anyone had a way to monitor for unused databases - meaning databases that were used at one point and now they just sit because no one retired them with the application. I know I could use profiler to monitor connections and whatnot, but I am looking for something long term.
Any thoughts? Thanks.
June 9, 2007 at 3:05 am
to keep them available, but avoid the actual resourceoverhead (ram-wize) just have the databases closed.
alter database xyz set AUTO_CLOSE ON .
Check BOL and remove it from db that get opened frequently !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 9, 2007 at 6:21 pm
I am not sure what the objective is, but if all you want is a way to know which database hasn't been used in a while just look into sysprocesses for any SPID > 50 grouped by dbid. The max of last_batch column will give you the last user-command that was run by database.
If you have dormant connections, they will show up with very old last-batch dates.
However you may still need to join on sysdatabases to catch databases that just no longer have connections open. They will have zero records in sysprocesses.
Hope this helps.
Herve Roggero
hroggero@pynlogic.com
MCDBA, MCSE, MCSD
SQL Server Database Proxy/Firewall and Auditing
June 11, 2007 at 8:55 am
Slick ALZ ... very slick ... scanning the SQL errorlog tells all ... far better than read only or offline ...
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
June 27, 2007 at 10:13 am
I tried setting Auto Close on a few of my databases and my Enterprise Manager connection starting filling up my log with entries of Starting up database 'xxx'. Is there something I'm missing or is this just standard behavior for EM?
Steve
June 27, 2007 at 11:42 pm
you could use profiler to see what is causing your db to startup again...
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
June 28, 2007 at 12:44 am
If you have auto-close option set to on then the database will be closed after the last user disconnects the connection to the database. After that when a new user wants to use the database the database will be recovered and then available to be used that why you find a line saying starting database ....
Moreover don't use that option unless otherwise necessarily needed as that will affect the performance of your database.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
June 28, 2007 at 6:05 am
The way I have done it is go into Query analyzer and do a SP_HELPDB, get the dbid, open up Profiler and run it with it only dbid equal to that number. You will then catch all SQL requests to that db only and you can see who, what and how often if any SQL requests still goto that db.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply