Stopping the trace because the current trace file is full

For sometime now, we were receiving alerts from one of the Production Instances. It read

Trace ID '4' was stopped because of an error.
Cause: Stopping the trace because the current trace file is full
and the rollover option is not specified..
Restart the trace after correcting the problem

As the message itself indicates one of the server-side trace output file on the instance had become full and it is stopping because the rollover option is not specified. Despite knowing the resolution I found it difficult to fix the issue because I could not find out from where this trace was getting triggered. After searching for trace related stored procedures in all the databases using this method, I was able to track down the one which was triggering this trace.

The resolution for the above error lies in the parameters used while executing sp_trace_create. The details of this stored procedure are documented on this article in MSDN. Since the error is related to trace file getting full, the parameters of interest are @maxfilesize and @options. @maxfilesize as the name suggests restricts the maximum size of the trace output file. @options decide the action to be taken when the trace file size reaches the @maxfilesize value. @options has three possible values.

  • TRACE_FILE_ROLLOVER (2)
  • SHUTDOWN_ON_ERROR (4)
  • TRACE_PRODUCE_BLACKBOX (8)

In my case @options had a value of 0 and @maxfilesize of 100MB. This trace was run by the audit team and it would be filled up in few minutes because the instance on which it was running is the busiest warehouse server in our environment.

I was not sure if the audit front-end application would be able to read “rolled over” trace files. Hence the option that I was left with was to increase the @maxfilesize from 100 MB to 20 GB. Now the count of these alerts has come down drastically but the warehouse instance eats up even 20GB once in a while!