Prev: Unable to shrink trans log due to log_reuse_wait_desc=REPLICATION
Next: [sqsrvres] CheckServiceAlive: Service is dead (Event ID: 19019)
From: dba on 28 Jan 2009 16:08
Hey All,

Hope this comes in handy to someone:

/* The system is alway running the default trace with traceid = 1 to
support DMVs */
/* Make sure no other trace is running, if so stop it */

DECLARE @maxTraceid INT;
DECLARE @killTraceID INT;
DECLARE @counter INT;
SET @maxTraceid = (SELECT MAX(id) FROM SYS.TRACES);
SET @counter = (SELECT COUNT(id) FROM SYS.TRACES);
IF (@maxTraceid) > 1 AND (@counter =2)
BEGIN;
PRINT 'One other trace is running, stop that one';
EXEC sp_trace_setstatus @traceid=(a)maxTraceid, @status = 0;
EXEC sp_trace_setstatus @traceid=(a)maxTraceid, @status = 2;
END;

IF (@maxTraceid) > 1 AND (@counter > 2)
BEGIN;
PRINT 'Multiple traces running stop each one except default';
DECLARE trace_cursor CURSOR
FOR SELECT id FROM SYS.TRACES WHERE (id > 1);

OPEN trace_cursor;
FETCH NEXT FROM db_cursor
INTO @killTraceID;

WHILE @@FETCH_STATUS = 0
BEGIN;

EXEC sp_trace_setstatus @traceid=(a)killTraceID, @status = 0;
EXEC sp_trace_setstatus @traceid=(a)killTraceID, @status = 2;

FETCH NEXT FROM trace_cursor
INTO @killTraceID;

END;

CLOSE trace_cursor;
DEALLOCATE trace_cursor;

END
ELSE
BEGIN
PRINT CAST(@counter AS VARCHAR(2));
PRINT 'Only the default trace is running, nothing to do';
END
 | 
Pages: 1
Prev: Unable to shrink trans log due to log_reuse_wait_desc=REPLICATION
Next: [sqsrvres] CheckServiceAlive: Service is dead (Event ID: 19019)