Have you ever encountered a situation where SQL Server unexpectedly shuts down by itself? It may sound strange, but it can happen. Recently, I had an interesting conversation with a fellow traveler on a flight who shared his experience of facing this issue. Intrigued by the problem, we exchanged business cards and I reached out to him later to gather more details about the SQL shutdown problem. In this article, we will explore the error message “An Error Occurred While Writing an Audit Trace” and discuss how to resolve it.

Upon analyzing the ERRORLOG from the SQL Server instance, I found the following 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 message itself provides some clues. SQL Server was shut down because it was unable to write to the trace file. However, the question remains: why did this happen?

One possible reason for this error is if C2 type auditing is enabled. However, in this case, the auditing option was set to zero. To gather more information, I asked the gentleman to share the script for the various traces that were running. He provided me with the following script:

exec @rc = sp_trace_create @TraceID output, 6, N'\\IndiaFileServer\T$\MSSQL_Trace\ProdServers\FooBar_Trace', @maxfilesize, NULL

According to the documentation, the value “6” in the script represents a combination of options 2 and 4. Option 2 specifies that the trace files should rollover after reaching the maximum file size specified by “@maxfilesize”. Option 4 indicates that if SQL Server is unable to write to the trace file, it will shut down. This behavior is by design for a security auditing feature.

To determine if the shutdown option is enabled or not, we can examine the running traces using the catalog view called “sys.traces”. Since we know that the trace is configured to shut down SQL Server in case of a failure to write, we decided to change the option to use “2” instead of “6”.

In the end, I was glad that we were able to identify the cause of the issue and solve the mystery behind the unexpected SQL Server shutdown.

If you ever encounter a similar problem where SQL Server shuts down unexpectedly, make sure to check for any error messages related to writing audit traces. Additionally, ensure that there is sufficient disk space available and consider adjusting the trace options to prevent automatic shutdowns.

Thank you for reading this article. I hope it provided you with insights into SQL Server shutdown issues and how to resolve them. If you have any questions or would like to share your own experiences, feel free to leave a comment below.