November 13, 2012 at 9:59 pm
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?
November 13, 2012 at 11:31 pm
Permissions issue with the directory that the trace file is being written?
November 14, 2012 at 4:12 am
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.
November 16, 2016 at 3:59 pm
Has this been resolved? We are having the same issue on two different SQL instances... We are using SQL Server 2014
July 2, 2018 at 12:03 pm
having the same issue, looking for a resolution.
July 2, 2018 at 3:00 pm
princesshaskins - Monday, July 2, 2018 12:03 PMhaving the same issue, looking for a resolution.
@princesshaskins - can you detail your exact situation? This thread mentions the @options parameter - code to highlight what's happening in your particular scenario would be helpful.
July 25, 2018 at 10:51 pm
I am having the same issue in sql server 2014:
The trace runs for about 30 minutes then shuts down sql, the drive has plenty of space
And appropriate permissions. I changed this trace to not shutdown but STIG requirements
say I must have it configured to shut down if it can’t write to the trace file
July 27, 2018 at 8:32 am
Just out of curiosity, has anyone ACTUALLY monitored disk space on the drive where the trace is being written while the trace is running? And especially at the time the SQL Service shuts down. I'd also wonder if anyone has quota management going on the drive where the trace is being written, with some kind of enforcement in play.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 27, 2018 at 9:13 am
Actually I narrowed my issue down to a specific trace event ID 43 SP Complete all the others are fine
But sql shuts down if I add that event
July 27, 2018 at 10:30 am
butterfly_lori - Friday, July 27, 2018 9:13 AMActually I narrowed my issue down to a specific trace event ID 43 SP Complete all the others are fineBut sql shuts down if I add that event
Okay, but did you try actually monitoring free disk space actively while the shutdown occurs. I'd want to know if there was increasing usage up until shutdown, followed by a sudden release of a chunk at shutdown... just to see if it's perhaps over-allocating space.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply