As a SQL Server DBA, there are times when you need to put on your detective hat and investigate issues reported by various teams. Whether it’s the application team, infrastructure team, or even your own curiosity, looking at the data points and connecting the dots can help you uncover the cause of the problem.

Let’s dive into the concept of audit traces in the SQL Server data folder. Imagine a scenario where the Windows team reports low free space on the DATA drive, which contains SQL database files. You, as the DBA, remember restricting the file size, so it’s unlikely that the issue is caused by MDF or LDF files. Upon further investigation, you come across files like “audittrace20170512091829.trc” and hundreds of similar files.

The “.trc” extension is commonly used by SQL trace files, indicating that traces are running and not stopping even after restarting SQL Server. This gives you a clue that some kind of auditing is enabled. To confirm your suspicion, you execute two queries: “sp_readerrorlog 0,1,’trace'” and “select id, path from sys.traces”. The output confirms that the files you found are indeed from an audit trace.

Next, you ask the team to check the value of “c2 audit mode” using the query “sp_configure ‘c2 audit mode'”. The result returns 1, indicating that C2 auditing is enabled on the server.

Now that you have identified the cause of the issue and the team is facing space constraints, it’s time to find a solution or workaround. Since they are unsure who enabled C2 auditing on the server, it is agreed to disable it. There are two ways to achieve this: either through the UI or using T-SQL.

Using T-SQL, you can execute the following commands:

EXEC sys.sp_configure N'c2 audit mode', N'0'
GO
RECONFIGURE WITH OVERRIDE
GO

Once the above steps are completed, you need to restart the SQL Server instance to stop the traces from capturing data.

Have you encountered similar detective work in your SQL Server journey? Feel free to share your experiences in the comments below.