2

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'.
2
  • 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 Nov 17, 2016 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. Nov 17, 2016 at 19:56

2 Answers 2

2

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
3
  • 1
    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. Nov 17, 2016 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 Nov 17, 2016 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'. Nov 29, 2016 at 17:21
1

This way is worked :

Set the 'show advanced option' to '1'

USE master;
GO
EXEC sp_configure 'show advanced option', '1';

Then run reconfigure:

RECONFIGURE;

Then show all configuration options;

EXEC sp_configure;

Verify that the 'C2 Audit Mode' config_value and run_value fields are set to '1'. If they are - that is what needs to be changed. You should be able to run the code to set the config_value to '0' (disabled).

EXEC sp_configure 'c2 audit mode', '0';

If you run EXEC sp_configure; again it should show the config_value is '0' and the run_value is still '1'. To turn off the c2 audit trace, you need to have the run_value set to '0' as well. To get this, run:

RECONFIGURE with OVERRIDE;

If that doesn't do it, then restart the SQL SERVER services and the config_value will get picked up.

reference

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.