Disable a Trace

[metevil80]

It seems on the rebbot of a server a trace had started and the audittrace*.trc files generated brought the drive space down to 0KB and stopped the Server. I have tried taking the trace off with this code
SELECT * FROM ::fn_trace_getinfo(default) WHERE property = 2;

DECLARE @TraceID int
SET @TraceID = 1
EXEC sp_trace_setstatus @TraceID, 0
EXEC sp_trace_setstatus @TraceID, 2

And the message I get is that I need to use SP_CONFIGURE. SO I tried disabling the C2 audit mode with this code

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

RECONFIGURE;
EXEC sp_configure;

And the run value for the C2 audit mode is still 1. I am hesitant to stop the SQL SERver service and start it with a start parameter of -f to get the C2 audit mode to stop. Any help would be appreciated. Thanks

 

[gmmastros]

try this:

In SQL Server Management Studio
Right click your server, click properties
Click on the security tab (top left).
Check your "Enable C2 audit tracing" configuration value.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

According to this document:

http://www.sqldbadiaries.com/2010/11/04/c2-auditing-mode-brought-down-the-instance/

You can simply delete older trace files if you want to keep C2 auditing enabled.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[metevil80]

The enable C2 audit tracing is currently unchecked. Truth be told I'm not exactly sure why the trace started, except for that we had to reboot the server after a Hotfix install. I do not want it to continue though.

 

[gmmastros]

Perhaps you could try enabling it and then disabling it?

You could also try executing the following TSQL code:

sp_configure 'show advanced options', 1
RECONFIGURE WITH OVERRIDE

sp_configure 'c2 audit mode', 0
RECONFIGURE WITH OVERRIDE

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[metevil80]

Thanks for all your help George. It seems that after I had run

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

RECONFIGURE;
EXEC sp_configure;

with no change on the run_value, I waited until after hours and just restarted the SQL Server services and everything took effect. So if in doubt, restart.