Cannot Delete Audit Trace File
Nov 17, 2006Hi,
I've created a few audit trace sessions and each of them is producing files.
The procedure I used is listed below:
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'sp_Turn_Audit_On' AND type = 'P')
DROP PROC sp_Turn_Audit_On
GO
CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1024
exec @rc = sp_trace_create @TraceID output, 2, N'E:Program FilesMicrosoft SQL ServerMSSQLTraceAudit_Info.trc', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
begin
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 6, @on
exec sp_trace_setevent @TraceID, 20, 9, @on
exec sp_trace_setevent @TraceID, 20, 10, @on
exec sp_trace_setevent @TraceID, 20, 11, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 13, @on
exec sp_trace_setevent @TraceID, 20, 14, @on
exec sp_trace_setevent @TraceID, 20, 16, @on
exec sp_trace_setevent @TraceID, 20, 17, @on
exec sp_trace_setevent @TraceID, 20, 18, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 13, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 16, @on
exec sp_trace_setevent @TraceID, 37, 17, @on
exec sp_trace_setevent @TraceID, 37, 18, @on
exec sp_trace_setevent @TraceID, 46, 1, @on
exec sp_trace_setevent @TraceID, 46, 6, @on
exec sp_trace_setevent @TraceID, 46, 9, @on
exec sp_trace_setevent @TraceID, 46, 10, @on
exec sp_trace_setevent @TraceID, 46, 11, @on
exec sp_trace_setevent @TraceID, 46, 12, @on
exec sp_trace_setevent @TraceID, 46, 13, @on
exec sp_trace_setevent @TraceID, 46, 14, @on
exec sp_trace_setevent @TraceID, 46, 16, @on
exec sp_trace_setevent @TraceID, 46, 17, @on
exec sp_trace_setevent @TraceID, 46, 18, @on
exec sp_trace_setevent @TraceID, 47, 1, @on
exec sp_trace_setevent @TraceID, 47, 6, @on
exec sp_trace_setevent @TraceID, 47, 9, @on
exec sp_trace_setevent @TraceID, 47, 10, @on
exec sp_trace_setevent @TraceID, 47, 11, @on
exec sp_trace_setevent @TraceID, 47, 12, @on
exec sp_trace_setevent @TraceID, 47, 13, @on
exec sp_trace_setevent @TraceID, 47, 14, @on
exec sp_trace_setevent @TraceID, 47, 16, @on
exec sp_trace_setevent @TraceID, 47, 17, @on
exec sp_trace_setevent @TraceID, 47, 18, @on
exec sp_trace_setevent @TraceID, 104, 1, @on
exec sp_trace_setevent @TraceID, 104, 6, @on
exec sp_trace_setevent @TraceID, 104, 9, @on
exec sp_trace_setevent @TraceID, 104, 10, @on
exec sp_trace_setevent @TraceID, 104, 11, @on
exec sp_trace_setevent @TraceID, 104, 12, @on
exec sp_trace_setevent @TraceID, 104, 13, @on
exec sp_trace_setevent @TraceID, 104, 14, @on
exec sp_trace_setevent @TraceID, 104, 16, @on
exec sp_trace_setevent @TraceID, 104, 17, @on
exec sp_trace_setevent @TraceID, 104, 18, @on
exec sp_trace_setevent @TraceID, 107, 1, @on
exec sp_trace_setevent @TraceID, 107, 6, @on
exec sp_trace_setevent @TraceID, 107, 9, @on
exec sp_trace_setevent @TraceID, 107, 10, @on
exec sp_trace_setevent @TraceID, 107, 11, @on
exec sp_trace_setevent @TraceID, 107, 12, @on
exec sp_trace_setevent @TraceID, 107, 13, @on
exec sp_trace_setevent @TraceID, 107, 14, @on
exec sp_trace_setevent @TraceID, 107, 16, @on
exec sp_trace_setevent @TraceID, 107, 17, @on
exec sp_trace_setevent @TraceID, 107, 18, @on
exec sp_trace_setevent @TraceID, 109, 1, @on
exec sp_trace_setevent @TraceID, 109, 6, @on
exec sp_trace_setevent @TraceID, 109, 9, @on
exec sp_trace_setevent @TraceID, 109, 10, @on
exec sp_trace_setevent @TraceID, 109, 11, @on
exec sp_trace_setevent @TraceID, 109, 12, @on
exec sp_trace_setevent @TraceID, 109, 13, @on
exec sp_trace_setevent @TraceID, 109, 14, @on
exec sp_trace_setevent @TraceID, 109, 16, @on
exec sp_trace_setevent @TraceID, 109, 17, @on
exec sp_trace_setevent @TraceID, 109, 18, @on
exec sp_trace_setevent @TraceID, 110, 1, @on
exec sp_trace_setevent @TraceID, 110, 6, @on
exec sp_trace_setevent @TraceID, 110, 9, @on
exec sp_trace_setevent @TraceID, 110, 10, @on
exec sp_trace_setevent @TraceID, 110, 11, @on
exec sp_trace_setevent @TraceID, 110, 12, @on
exec sp_trace_setevent @TraceID, 110, 13, @on
exec sp_trace_setevent @TraceID, 110, 14, @on
exec sp_trace_setevent @TraceID, 110, 16, @on
exec sp_trace_setevent @TraceID, 110, 17, @on
exec sp_trace_setevent @TraceID, 110, 18, @on
exec sp_trace_setevent @TraceID, 115, 1, @on
exec sp_trace_setevent @TraceID, 115, 6, @on
exec sp_trace_setevent @TraceID, 115, 9, @on
exec sp_trace_setevent @TraceID, 115, 10, @on
exec sp_trace_setevent @TraceID, 115, 11, @on
exec sp_trace_setevent @TraceID, 115, 12, @on
exec sp_trace_setevent @TraceID, 115, 13, @on
exec sp_trace_setevent @TraceID, 115, 14, @on
exec sp_trace_setevent @TraceID, 115, 16, @on
exec sp_trace_setevent @TraceID, 115, 17, @on
exec sp_trace_setevent @TraceID, 115, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto noCursor
error:
select ErrorCode=@rc
noCursor:
return
end
Unfortunately, I didn't mention the @stoptime for each of the trace sessions and now I'm unable to delete the trace files. Is there any select statement that will be useful in finding the @traceid for these auditting sessions? Also, how am I able to stop the sessions without stopping the related services?
Thank you in advance.
Best regards,
Alla