March 6, 2009 at 11:37 am
Is there a way to limit the number of trace files produced in a server side trace using the rollover option? I'd like to have a server side trace run for some time without having to worry about filling up the drive. It appears default trace only keeps 5 files, but I haven't found yet how that is enabled. Any ideas? Am I missing something? Many thanks.
March 6, 2009 at 12:31 pm
The number of rollover files is one of the optional paramters for sp_trace_create
(From Books Online)
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
[ , [ @filecount = ] 'max_rollover_files' ]
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2009 at 12:47 pm
Thanks, when I initially saw that I interpreted it to mean the trace would stop once the max files was reached.
March 6, 2009 at 12:59 pm
From Books online
[ @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.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 6, 2009 at 1:15 pm
jason dodd (3/6/2009)
Is there a way to limit the number of trace files produced in a server side trace using the rollover option? I'd like to have a server side trace run for some time without having to worry about filling up the drive. It appears default trace only keeps 5 files, but I haven't found yet how that is enabled. Any ideas? Am I missing something? Many thanks.
Yes the default trace only keeps 5 files and that is all enabled at startup.
As Gail has shown you need to use the @filecount parameter to set the number of roll over files, you also need to set the @options parameter to 2 in order to enable roll over files. When you script a trace definition using Profiler it sets the @options parameter to 0 and this does not allow for rollover files.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 27, 2009 at 9:45 am
Thank you all for the help, this has been working great on SQL 2005. I know this is a SQL 2005 forum and I can open a new topic in the SQL 2000 forums if that is more appropriate, but I have a similar question for SQL 2000. It doesn't look like the sp_trace_create procedure has a @filecount parameter. We have quite a number of SQL 2000 servers still and I'd like to do the same sort of tracing for them.
Does anyone know if it's possible to limit the number of rollover files in SQL 2000? I've thought about using a stop time which may work out fine so long as I filter my trace files to keep the size down. Is there a way to load a SQL 2005 sp_trace_create (modified for SQL 2000 using a different name) into my SQL 2000 instance? I'm not having luck scripting it out though...
Thanks.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply