Trace shuts sql service down

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 - General » Trace shuts sql service down

Trace shuts sql service down

Rate Topic

Display Mode

Topic Options

Author

Message

sqlpadawan_1

Posted Tuesday, November 13, 2012 9:59 PM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 81, Visits: 465

Greeting All,

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?

Post #1384381

Lynn Pettis

Posted Tuesday, November 13, 2012 11:31 PM

SSC-Insane

Group: General Forum Members
Last Login: Today @ 7:50 PM
Points: 21,624, Visits: 27,465

Permissions issue with the directory that the trace file is being written?

Cool

Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)

Post #1384395

sqlpadawan_1

Posted Wednesday, November 14, 2012 4:12 AM

SSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:48 PM
Points: 81, Visits: 465

I wondered about that. The SQL service is running under a local admin account. The permissions for the directory looked normal.

The trace could create the file, and write some data to it under both circumstances (@options = 2 and @options = 6). But it was shutting down under @options = 6 when the trace file was 1MB on disk. I didn't have enough time to see if it would shut down when @options = 2 and the trace file reached 1MB, it grew at a much slower rate.

Post #1384531

« Prev Topic | Next Topic »

Permissions