Have you ever been in a situation where SQL Server shuts down by itself? It sounds strange but I met across a gentleman in flight who reported this issue to me. Since I was curious to know the cause, we exchanged our business cards. Once I came back home, I sent him an email asking more details about the SQL shutdown issue. Let us learn about how to fix error, An Error Occurred While Writing an Audit Trace.
I asked him to share ERRORLOG from the SQL Server instance and I could find below information just before the shutdown.
spid397 Error: 566, Severity: 21, State: 1.
An error occurred while writing an audit trace. SQL Server is shutting down. Check and correct error conditions such as insufficient disk space, and then restart SQL Server. If the problem persists, disable auditing by starting the server at the command prompt with the “-f” switch, and using SP_CONFIGURE.
The error is kind of self-explanatory. SQL was shut down because it was not able to write to trace file, but the real question is why?
One of the possible way this error would come up would be if we were doing C2 type auditing.
But in the above case we can see that it’s set to zero. Then I have asked for more details about various traces which were running and he provided me below script.
exec @rc = sp_trace_create @TraceID output, 6, N’\\IndiaFileServer\T$\MSSQL_Trace\ProdServers\FooBar_Trace’, @maxfilesize, NULL
As per books online, the 6 above is a combination of options 2 & 4. Option 2 makes file rollover after they reach @maxfilesize. Option 4 specifies that if SQL ever can’t write to the trace file, it will shut down the SQL Server. This is by design for a security auditing feature.
To find if shutdown option is enabled or not, we can look at running traces using catalog view called as sys.traces.
Since we know that trace is configured to shutdown SQL server in case of failure to write. He agreed to change option to use 2 instead of 6.
At the end, I was happy that we could find cause and solve a mystery.
Reference: Pinal Dave (https://blog.sqlauthority.com)
1 Comment. Leave new
hello. I am new in SQL administration. I encountered the same issue referenced above.
I ran the query “select * from sys.traces” to display the traces. There were 2 traces. Trace ID1 had the value of field “is_shutdown”=0 but trace ID2 had the value of field “is_shutdown” =1. So the problem is trace ID2 with the value of “is_shutdown=1”. But when I tried to change the @option to 2 (from 6) or change the value of “is_shutdown” to 0, I get a message about the stored procedure (sp_trace) not found. These are the queries I ran unsuccessfully.
1 — “exec sp_trace_setis_shutdown @traceid=2, @is_shutdown=0” –>to change the value of the “is_shutdown” for that trace ID2 from 1 to 0.
2 — “exec sp_trace_setoptions @traceid=2, @options=2”
However, as a workaround, I ran the following queries to stop and disable/delete the trace ID2 temporarily. This allowed the SQL server service to run but would only fail after the next server reboot.
exec sp_trace_setstatus @traceid=2, @status=0 —-> to stop the trace then
exec sp_trace_setstatus @traceid=2, @status=2 —> to disable/delete it temporarily.
How can I change the value of the @options to 2 (from 6) or change “is_shutdown” to 0 for trace ID2? Please help.