0

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'.
  • Hmm - from the file path and name of your trace, this 'looks' like the default trace - check this link - msdn.microsoft.com/en-us/library/ms175513.aspx – Scott Hodgin Nov 17 '16 at 19:45
  • Sorry, I forgot to mention, it is the audittraceXXX.trc file that is the one filling up the drive. The default trace (log_XXX.trc) is cycled after 5 files so it isn't the one filling up the drive. – Geoff Dawdy Nov 17 '16 at 19:56
1

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
| improve this answer | |
  • Despite being a SysAdmin I am getting the error "You do not have permission to run 'SP_TRACE_SETSTATUS'. I tried doing a "GRANT EXEC" on the proc but I'm still getting permission errors. – Geoff Dawdy Nov 17 '16 at 20:21
  • Did you already run Exec sp_configure 'c2 audit mode', 0; and either restart the instance or run reconfigure? I read where you'd get that permission error if the c2 trace was still enabled. - From this link - yqcomputer.com/1_fa0a12ab061e733d_1.htm – Scott Hodgin Nov 17 '16 at 20:25
  • Sorry for the delay on response. I have confirmed c2 audit mode is disabled. The instance has been restarted. I am still getting the permission error on 'SP_TRACE_SETSTATUS'. – Geoff Dawdy Nov 29 '16 at 17:21

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.