Yesterday the application team reached out to our team stating that the drive hosting SQL Server databases was running out of space. It is not something new for our team that the Application Support team holds the database as the reason for any issue they face with the Application. But this time even we were alerted that the E drive on that server was running out of space. When the DBA logged on to the server, the drive had 0 KB free space left!
A quick glance on that drive indicated that .trc files ~30 GB in size were there on that drive. The folder hosting these files looked like this.
Since all these files had the naming convention of audittrace*.trc, it was very clear that these files were generated becuase the C2 Audit mode was enabled. In order to check the configuration when the DBA tried connecting to the instance, the fact that the instance was down came into light! The SQL Server Error Log had this information in the end.
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. SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.
The SQL Server got stopped when it was trying to write to an audit trace file. Troubleshooting this issue is very easy if one know how C2 Audit mode works. Here are some of the features of C2 Audit mode in SQL Server.
- The output of C2 auditing trace files is saved to the Default Data directory for an instance.
- There is no option to change the path of the C2 auditing trace files to a directory other than this.
- When the trace file size reaches 200MB, it is automatically rolled over and a new trace file is created.
- There is no built-in functionality to delete the old trace files. SQL Server will keep generating the trace files until the drive gets full.
- A custom job needs to be setup which deletes trace files older than x number of days.
- If the drive where the trace files are saved gets full, SQL Server will shutdown itself.
The last point was key in this case. If the drive where the audit trace files are getting saved runs out of space, SQL Server stops the SQL Server service. To overcome this issue there are two options. First is to clear space on the drive where the audit trace files are saved. Second option is to start the SQL Server service with -f flag which bypasses auditing and then disabling C2 Audit mode.
C2 audit mode can be disabled either by T-SQL or SSMS as below.
sp_configure 'c2 audit mode',0 go reconfigure with override go
In our case when we asked the client if C2 Audit needs to be enabled, he was clueless why it was enabled in the first place. Most likely when the instance was built, the DBA enabled this feature by mistake. Now that the client does not require this feature to be enabled, we are processing a Change Control to disable it.