SQL SERVER – Why We have Audit Trace in DATA Folder? What are These Files?

There are many times when a DBA should work like a detective and find the cause of the issue which is reported by various teams. Sometimes it is an application team, sometimes an infrastructure team and sometimes its own learning. I always believe looking at the data points and finally connect the dots to make a story. Let us learn about why we have audit trace in the data folder.

One fine day, the windows team reported an issue of low free space on the DATA drive, which contains SQL database files. DBA clearly remembered restricting the size of file so there are less chances that it was caused due to MDF or LDF files. He looked around and found files like audittrace20170512091829.trc and 1000 of such files.

The extension .trc is generally used by the SQL trace files and we were sure that we have traces running and it’s not getting stopped even after they restart SQL Server. This gave us indication that must be some kind of auditing which is enabled. I asked them output of two queries.

sp_readerrorlog 0,1,'trace'

and

select id, path from sys.traces

Here was the output

This made things clear that the file which they are seeing were from audit trace. I asked them to check

sp_configure 'c2 audit mode'

And sure enough, it returned 1 which means C2 audit was enabled.

SOLUTION / WORKAROUND

Since they were having space issue and they were not sure who enabled C2 auditing on the server, there was OK to disable it.

There are two ways to do that either from the UI as shown below

or using T-SQL

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

Once the above is done, we need to restart the SQL instance to stop those traces to capture the data.

Have you come across such detective stuff? Please share via comments.

Reference: Pinal Dave (http://blog.SQLAuthority.com)