THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Kalen Delaney

Did You Know? A 6th parameter is available when creating a trace

Most people I know, as geeky as some of them are, prefer to create their server-side trace definitions by letting the Profiler GUI do the dirty work. It's so much nicer to just point and click your way to defining a set of trace events, data columns and filters, than having to write dozens or even hundreds of calls to sp_trace_setevent. ( The sp_trace_setevent procedure has to be called once for every combination of event and data column, so for even the smallest trace, with half a dozen events and half a dozen data columns, that would be 36 calls.)

Believe me, I'm not knocking this wonderful capability of having the SQL Server Profiler automatically create my trace definition for me, but whoever updated this mechanism for SQL Server 2005 forgot to check the BOL and see that there is a new parameter to sp_trace_create that wasn't in SQL Server 2000. This parameter does not show up in the automatically generated trace create script, and you have to add it manually if you want it.

Here is the call that I just generated automatically:

exec @rc = sp_trace_create @TraceID output, 0, N'InsertFileNameHere', @maxfilesize, NULL

The first parameter is the traceID, which as an output parameter, will be returned, and will then be used in all subsequent calls to sp_trace_* procedures.

The second parameter is a bitmap indicating which of various options are on.  0 means none of the options are enabled. See the BOL for details of the possible values.

The third parameter is, hmm, let me think about it a bit. Oh yeah, it's the full path and file name to save your trace data to. It must be a N'Unicode' string and SQL Server will always add a .trc suffix.  So if I called my trace N'C:\mytrace.trc', the name that Explorer would show me would be mytrace.trc.trc. Windows doesn't care, but you might.

The fourth parameter is a variable to indicate the maximum size of any output file. (A value of 5 was assigned to this variable earlier in the script.)

The fifth parameter is the stop time. Since I didn't set a stop time when creating my trace in Profiler, no stop time is listed here, but I can change that if I choose to.

The missing sixth parameter can be found by looking in Books Online for sp_trace_create and seeing this:

 

[ @filecount = ] 'max_rollover_files'

Specifies the maximum number or trace files to be maintained with the same base filename. max_rollover_files is int, greater than one. This parameter is valid only if the TRACE_FILE_ROLLOVER option is specified. When max_rollover_files is specified, SQL Server tries to maintain no more than max_rollover_files trace files by deleting the oldest trace file before opening a new trace file. SQL Server tracks the age of trace files by appending a number to the base file name.

For example, when the trace_file parameter is specified as "c:\mytrace", a file with the name "c:\mytrace_123.trc" is older than a file with the name "c:\mytrace_124.trc". If max_rollover_files is set to 2, then SQL Server deletes the file "c:\mytrace_123.trc" before creating the trace file "c:\mytrace_125.trc".

Notice that SQL Server only tries to delete each file once, and cannot delete a file that is in use by another process. Therefore, if another application is working with trace files while the trace is running, SQL Server may leave these trace files in the file system.

This parameter is very cool, in part because there is no way to specify a maximum number of files when defining your trace through the GUI. So I guess that's the reason it doesn't appear in the auto-generated script. But it's nice to know that this option is available, as a way to keep large traces on a busy server from eating up all your available disk space.

Have fun!
Kalen

Published Friday, June 29, 2007 7:45 PM by Kalen Delaney

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

RichB said:

That seems pretty handy, much like a tailorable black box trace I guess?

July 3, 2007 10:13 AM
 

Kalen Delaney said:

Good point... with this option you can now create a trace of your own that does exactly what the blackbox does, but you can control where to store the output, what to call it, etc, etc....

~Kalen

July 3, 2007 11:40 AM
 

Kevin Bailey said:

Was just checking into this, because the Default Trace (sp_configure 'default trace enabled') appears to have rollover set to 5 files. But fn_trace_getinfo(DEFAULT) does not show the value for @filecount. Any idea how to obtain @filecount once a server trace is running? (I'm trying to think of why I need to know that... I just do!)

Thanks,

Kevin

January 29, 2008 12:06 PM
 

Kalen Delaney said:

Hi Kevin

fn_trace_getinfo has not been updated for SQL 2005. You should be using the new sys.traces. Lots more info!

~Kalen

January 29, 2008 2:32 PM
 

Kevin Bailey said:

Yes, sys.traces, thanks. Seems like a bit of a hodge-podge internally, because fn_trace_getinfo hasn't been updated, but the others (fn_trace_geteventinfo, fn_trace_getfilterinfo) seem to be all we have for event and filter info on active traces.

January 30, 2008 1:12 PM
 

Karch said:

Hi Kalen

Is it at all possible to change the properties of the default trace? Number of trace files and their size, per se.

I've heard several times that it is not possible, but anyway there should be a way to do this, as SQL Server should read that information from somewhere. Probably, some registry key?

June 16, 2008 10:35 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Favorite Non-technical Sites or Blogs

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement