I've come across a server that, when I start a trace, within moments the SQL service shuts itself down. The trace is being started from a stored proc that calls the sp_trace_create procedure:
EXEC @rc = sp_trace_create @TraceID OUTPUT, @options = 6, @tracefile = @TraceFile,
@maxfilesize = @MaxFileSizeInMB, @stoptime = @StopDateTime, @filecount = @MaxFileCount
An example looks like this:
EXEC [dbo].[DefaultTrace] @MaxFileSizeInMB = 50, @StopDateTime = '2012-11-13 11:00 PM',
@MaxFileCount = 999, @TraceFile = 'd:\temp\testing',
@MinDurationFilter = 10000;
The trace starts, the target file grows to 1MB, and then SQL shuts down.
There are a few entries in the sql error log:
11/13/2012 22:03:59,spid52,Unknown,Trace ID '2' was stopped because of an error. Cause: 16534123(failed to retrieve text for this error. Reason: 15100). Restart the trace after correcting the problem.
11/13/2012 22:03:59,spid52,Unknown,Error: 19099<c/> Severity: 16<c/> State: 1.
11/13/2012 22:03:59,spid52,Unknown,SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
11/13/2012 22:03:59,spid52,Unknown,An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space<c/> and then restart SQL Server. If the problem persists<c/> disable auditing by starting the server at the command prompt with the "-f" switch<c/> and using SP_CONFIGURE.
11/13/2012 22:03:59,spid52,Unknown,Error: 566<c/> Severity: 21<c/> State: 1.
11/13/2012 22:03:37,spid78,Unknown,SQL Trace ID 2 was started by login "sa".
I understand why SQL is being shutdown, it's the @options parameter in the sp_trace_create call. The 6 represents TRACE_FILE_ROLLOVER + SHUTDOWN_ON_ERROR. I don't understand what is causing the problem.
I'm pretty confident that I can remove the SHUTDOWN_ON_ERROR and the trace will work. It was taking too long to get to the 1MB threshold on the trace file and my maintenance window closed. I've used this trace proc ( w/ @options = 6 ) on so many servers in the last year, this is the first time I've had this problem. It makes me believe something else is going on.
I had my research assistant (aka Google) look around and everything I could find pointed to C2 auditing (which I confirmed was off), or insufficient disk space (which isn't the case, plenty of disk on all drives).
Where else do I look?