Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2006
    Posts
    31

    Unanswered: Cannot Delete Audit Trace file

    Hi,

    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 Files\Microsoft SQL Server\MSSQL\Trace\Audit_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

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Code:
    select *
    from ::fn_trace_getinfo(default)
    You will likely need to look into this function, as well:
    Code:
    sp_trace_setstatus [ @traceid = ] trace_id 
        , [ @status = ] status

  3. #3
    Join Date
    Nov 2006
    Posts
    31
    This works!!! Thank you so much for your help!!!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •