Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
Peace2007
Posting Yak Master
239 Posts |
 Posted - 2009-02-23 : 04:59:23
|
| what's the difference between closing server side trace and stopping it? |
|
|
heavymind
Posting Yak Master
115 Posts |
Posted - 2009-02-23 : 05:42:59
|
| Stopping and Closing TracesWhen a trace is first created, it has the status of 0, stopped (or not yet started, in that case). A trace can be brought back to that state at any time, using sp_trace_setstatus. To set trace ID 2 to a status of stopped, the following Transact-SQL code is used: Copy CodeEXEC sp_trace_setstatus 2, 0Aside from the obvious benefit of the trace no longer collecting data, there is another perk to doing this: Once the trace is in a stopped state, you can modify the event/column selections and filters using the appropriate stored procedures, without re-creating the trace. This can be extremely useful if you only need to make a minor adjustment.If you are actually finished tracing and do not wish to continue at a later time, you can remove the trace definition from the system altogether by setting its status to 2: Copy CodeEXEC sp_trace_setstatus 2, 2Note Trace definitions will be automatically removed in the case of a SQL Server service restart, so if you do need to run the same trace again later either save it as a Profiler template or save the script used to start it.Thanks, VadymMCITP DBA 2005/2008Chief DBA at http://www.db-staff.com |
 |
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-23 : 06:07:00
|
| Thanks Vadym for the detailed description :)I'm having another question: actually, I've started a server side trace yesterday and when I queried fn_trace_getinfo, it told me that it's run. The server has not been restarted and neither has been logged of but the trace is in stop status now! Can some other events stop the server side trace? |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-02-23 : 06:13:57
|
If trace file gets full.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2009-02-23 : 07:19:22
|
| Have you specified stop time in it? |
|
|
|
Peace2007
Posting Yak Master
239 Posts |
Posted - 2009-02-23 : 08:11:59
|
| When we create a trace via profiler, it creates another file when the first one is full. doesn't server side trace do that? I'd specified an stop time but it stopped before that time! |
|
|
|
heavymind
Posting Yak Master
115 Posts |
Posted - 2009-02-23 : 09:23:46
|
| sp_trace_create has param[ , [ @filecount = ] 'max_rollover_files' ]which is optional and is apparently 1 by default. thus if you need more files to rollover - change this paramThanks, VadymMCITP DBA 2005/2008Chief DBA at http://www.db-staff.com |
|
|
|
heavymind
Posting Yak Master
115 Posts |
|
|
|
|
|
|
|
server side trace