Post reply

sql2005 Server side Trace

  • December 11, 2008 at 6:52 am

    #202763

    I was hoping someone may have some thoughts how to address an issue I have with a custom BlackBox trace that I need to setup as a startup proc and ensure the trace fiel is unique.

    The sql blackbox trace generates way too much data for our needs and c2 audit is not needed so I took the blackbox trace and modiified it with my filters and it runs fine. The issue is I am required to have it running at all times . So accounting for unexpected sql stop and starts I configured for it to start on sql start up.

    The issue I have is this trace generates files rolling over appending 1,2,4 etc.

    My concern is when we have an outage the trace will go back to the original file name thus the trace will not start.

    So trying to avoid renaming files via xp_cmdshell moving etc I thought I could build a string and append a unique value to the end.

    the problem I am having is getting the @string to execute.

    I believe this has something to do with the @rtraceid.

    Here is the original code and below is it modified building a string.

    If anyone has any suggestions on the best way to ensure a custom server side trace file is unigue everytime it starts please send it my way.

    -- orginal works fine

    exec @rc = sp_trace_create @TraceID output, 2, N'E:\tracedata\audit', @maxfilesize, NULL

    if (@rc != 0) goto error

    -- this genrates a unique file name but I cannot execute it I get an error.

    declare @rc int

    declare @TraceID int

    declare @maxfilesize bigint

    declare @newid nvarchar(40)

    declare @string nvarchar(4000)

    set @maxfilesize = 50

    SET @newid = newid()

    select @string = 'sp_trace_create @traceid output, 2, N'+''''+'E:\tracedata\pci_audit_'+

    substring(@newid,1,4) + ',' + (case when @maxfilesize is null then '1' else (cast (@maxfilesize as char(10))) end) + ', NULL '

    --if (@rc != 0) goto error

    exec @string

    Msg 2812, Level 16, State 62, Line 14

    Could not find stored procedure 'sp_trace_create @traceid output, 2, N'E:\tracedata\pci_audit_888D,50 , NULL '.

  • July 27, 2010 at 12:22 pm

    #1198893

    It looks to me like the trace file ID suffix is being captured in hexadecimal, and needs to be converted to decimal in order for the file name to be found.

Viewing 2 posts - 1 through 1 (of 1 total)

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