October 24, 2011 at 8:14 am
Some time ago we had a instant messenger service here, but we abandoned it some years ago. However, the SQL Server database is still around. I'd like to try and determine if anything is still using that database, before I drop it. Should I take it offline for a few days, maybe a week, and see what happens?
Kindest Regards, Rod Connect with me on LinkedIn.
October 24, 2011 at 8:22 am
if you're unsure, use a server side trace for a week or so to monitor user connections before decommissioning
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2011 at 8:52 am
That's a good idea, and definately one I hadn't thought of. I've only done a few traces, and all of them were ones I did interactlvely. How do I set up a server side trace, so that it will run, and keep on running, even when I get out of SSMS and log off of the server?
Kindest Regards, Rod Connect with me on LinkedIn.
October 24, 2011 at 8:55 am
open profiler, select the events you want to capture, run the trace for couple of seconds then stop.
Then export the trace definition and make changes if you want to (eg trace file path, stop time etc). Run that script in SSMS.
within profiler click on
File>Export>Script Trace Definition > For SQL Server 2005
October 24, 2011 at 8:58 am
don't forget to set the rollover option too, by default it scripts out to zero which means no file rollover. This puppy catches a lot of people out. Also be aware that the path\file you specify is a location that sql server has access to on the server, not your workstation 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2011 at 9:23 am
OK, simple question here, but I need to ask it. I was under what must be a mistaken thought that traces could run only against 1 database. It looks to me as though they run against all databases an instance of SQL Server has. Is that correct?
If the answer to that is yes, then how do I get it to only run against the database I'm interested in?
Kindest Regards, Rod Connect with me on LinkedIn.
October 24, 2011 at 9:37 am
Rod at work (10/24/2011)
OK, simple question here, but I need to ask it. I was under what must be a mistaken thought that traces could run only against 1 database. It looks to me as though they run against all databases an instance of SQL Server has. Is that correct?If the answer to that is yes, then how do I get it to only run against the database I'm interested in?
By using column filters 😉
Example from a trace script below
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 35, 1, 6, N'mydb'
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2011 at 9:39 am
Perry Whittle (10/24/2011)
Rod at work (10/24/2011)
OK, simple question here, but I need to ask it. I was under what must be a mistaken thought that traces could run only against 1 database. It looks to me as though they run against all databases an instance of SQL Server has. Is that correct?If the answer to that is yes, then how do I get it to only run against the database I'm interested in?
By using column filters 😉
Example from a trace script below
Clearly that db is not in use anymore. Drop it :hehe:.
October 24, 2011 at 9:41 am
i pasted but it did nuttink. have corrected it now 😉
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
October 24, 2011 at 9:45 am
Perry Whittle (10/24/2011)
i pasted but it did nuttink. have corrected it now 😉
My way's funnier :-D.
October 24, 2011 at 10:02 am
change you db so it uses "auto_close"
( alter database xyz set auto_close ON )
This way your applications - if any - should be able to work, if their timeout survives a db startup.
Then monitor the default trace for this event.
(unless you really want the apps to fail)
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
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply