Sql-server – Unable to remove trace from sys.traces

auditdisk-spacesql servertrace

We recently discovered one of our drives was being filled up by audittrace files. One of our DBAs turned off C2 auditing which should normally stop the files filling up the drive. However, it appears turning off C2 auditing has not had any affect.

I've tried looking at the existing traces to determine why the files are still being written.

SELECT * FROM sys.traces

ID    Status    Path
1     1         \\?\D:\MSSQL11.MSSQLSERVER\MSSQL\Data\audittrace20161113132900_260.trc
2     1         D:\MSSQL11.EPDM\MSSQL\Log\log_485.trc

ID 2 is currently set to the default but cycles after a max 5 files. This trace isn't the one filling up my hard drive.

ID 1 path and trace file are the ones filling up.

I've already turned off C2 auditing. What other troubleshooting can I do to see why the audittrace files are even being written to in the first place?

EDIT: I've restarted the services. It would seem the correct solution would be to turn off this audit trace by using the sp_trace_setstatus 1,0 but when I run that command I get the error:

Msg 8189, Level 14, State 32, Procedure sp_trace_setstatus, Line 6
You do not have permission to run 'SP_TRACE_SETSTATUS'.

Best Answer

I found the following information at this link which might be of some help:

SELECT * FROM sys.traces
WHERE path LIKE N'%audittrace_%'

Find the trace that is called audittrace.trc:
--Stop the trace
EXEC sp_trace_setstatus @traceid = <traceid>, @status = 0

--Delete the trace definition from the server
EXEC sp_trace_setstatus @traceid = <traceid>, @status = 2
Related Question