Idera SQL Complaince - Creating Trace files

Question

Post reply

Idera SQL Complaince - Creating Trace files

Tara-1044200

SSCoach

Points: 15948
More actions
April 3, 2012 at 10:36 am

#271478

I have unstalled the Idera SQL Complaince software on the sql server machine but still the trace files are creating (.trc files), how could i stop them.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 1

Start here: http://msdn.microsoft.com/en-us/library/ms176034.aspx

Lowell SSC Guru Points: 323504 More actions · Answer 2

do a quick SELECT * FROM sys.traces

if you have C2 auditing on, id=1 is the c2 trace, and id=2 is the default DDL trace;

otherwise id=1 is the default DDL trace.

everything else is traces you created , or that idera created;

so for example, if you want to stop and drop traceid=3,

it's a simple pair of commands:

declare @traceid int

SET @traceid =3 --the trace i visually reviewd and KNOW i want to stop

--exec sp_trace_setstatus @traceid , 0 ---stop trace, you must know the traceid to stop it

--exec sp_trace_setstatus @traceid , 2 ---close trace you must know the traceid to delete it

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Lowell SSC Guru Points: 323504 More actions · Answer 3

if you want to script out the trace just in case you need to put it back again, I've posted a very nice TSQl for it you can find here:

sp_ScriptAnyTrace.txt

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Tara-1044200 SSCoach Points: 15948 More actions · Answer 4

Tara-1044200

SSCoach

Points: 15948

April 3, 2012 at 10:55 am

#1468353

Thanks so much Lowell, that did it.

tonyf SSC Veteran Points: 264 More actions · Answer 5

For future reference and also you may like to check if the SQLcm stored procedures have been deleted

SYMPTOM:

After stopping, removing, or disabling the auditing components for a server, SQL trace files continue to be recorded in the \AgentTraceFiles directory.

CAUSE:

When the SQL compliance agent is stopped, uninstalled, or when server auditing is disabled, the service is designed to stop all running traces. If the agent fails to respond, traces may continue on the audited server, and will be cached in the \AgentTraceFiles directory.

Also, the SQL compliance stored procedures, which exist in the master database, may not be removed on uninstallation and will not be removed when auditing is disabled. Restarting the SQL Server service for the audited server will restart the traces specified by these stored procedures.

SOLUTION:

The SQL traces can be stopped manually in SQL Server. Drop the stored procedures to prevent this issue from recurring.

To manually delete the traces, run this statement to get a list of traces:

SELECT * FROM ::fn_trace_getinfo(NULL)

Then run each of these statements for each trace id you want to remove:

EXEC sp_trace_setstatus @traceid = <traceid> , @status = 0

EXEC sp_trace_setstatus @traceid = <traceid> , @status = 2

The names of the stored procedures to drop are

sp_sqlcompliance_audit

sp_sqlcompliance_startup

These procedures exist in the master database.

John Dunn-394774 SSC Rookie Points: 28 More actions · Answer 6

Ran into this exact problem on an older server where someone (long gone) had installed Idera DM a couple of years ago and then disabled. The problem was hidded because the C drive was HUGE (250Gb), but surfaced when the drive finally filled and caused a performance issue. I followed the procedure provided above and the problem was quickly fixed. Thanks for posting! (and thanks SQLServerCentral for keeping this post active on your site!)