September 9, 2009 at 7:48 am
I am using (exec @rc = sp_trace_create @TraceID output, 0, @filename, @maxfilesize, NULL ) to create a trace. I want this trace to start when the server restarts. I have written a stored procedure to create the trace, set event, and start the trace. As it stands, when the server restarts, the trace is trying to overwrite the old trace file and failing.
Ideally, I would like the new trace to append to the original series of trace files. I have seen an article which mentions this feature http://msdn.microsoft.com/en-us/library/ms189034.aspx
Does anyone know how to implement this?
September 14, 2009 at 5:03 am
Looks like appending to the current file isn't an option although the article suggests it is.
To prevent the loss of trace data when starting a new trace you either need to write to a new trace file with a different name or store the old trace data somewhere and restart the original trace with the same filename.
Using a new file name is not desirable as you will need to store the filename used each time in order to refer to the trace or the file.
The solution I went for was a stored procedure to import data from the trace file in to a database table, delete the old trace files and restart the trace with the same file name. This sproc is then set to execute when the server starts so all trace data is kept and trace is always running. The sproc can also be scheduled so trace data is imported to the db regularly.
Code attached.
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply