An error occurred while writing an audit trace. SQL Server is shutting down

There was this incident at one of the clients in recent past. It was a nice morning. I was enjoying the tea of the day. I just started my shift. And, just a few minutes later after assuming the pager. The mobile screen flashed with the message, a welcoming message. The message on my phone says An error occurred while writing an audit trace. SQL Server is shutting down.

Just like what I have always done. I Logged on to the system. The SQL Server is not up and running. Tried to start the Service manually and it failed. I have then started reviewing the error logs. SQL Server Error log has messages which read like below:

An error occurred while writing an audit trace. SQL Server is shutting down.
And
If the problem persists, disable auditing by starting the server at the command prompt with the "-f" switch, and using SP_CONFIGURE.

An error occurred while writing an audit trace. SQL Server is shutting down.

Here are the steps I took to resolve the issue I had, I was able to successfully resolve the issue. But that’s the temporary fix and doesn’t guarantee reoccurrence. At the end of this post, I will post a link that might help you fix this issue permanently.

1. Since this machine is the production instance I have decided to start the server first with SQLCMD using -f switch
2. Reviewed the sys.traces table, there is a default trace enabled. And, it occurred to me that there is an issue
   1. The trace has is_shutdown = 1. Which means it can shut down the SQL Server
   2. The NTFS permission to the Log path
3. Ran sp_trace_setstatus 2,0 to stop the default trace
4. The SQL Server has started successfully

So at this moment, the issue is fixed. It’s a temporary fix though. Did research on this very issue and found a link from Microsoft. It says, the SQL Service shuts down after SP2 is installed on SQL Server 2016 and 2017 with C2 Audit mode enabled.

In this case, the client is running on SQL Server 2017 and has SP2 installed. So I have recommended the customer to install CU12 for SQL Server 2017. This has fixed the issue permanently. The issue didn’t reoccur, at least until now.

In case you like this blog posts interesting I would like to encourage you to browse through some additional troubleshooting and configuration tips. Please let me know how do you like this post in the comment section below.

photo credit: jjbers Closed Rite Aid (Waterford, Connecticut) via photopin (license)