December 11, 2008 at 6:52 am
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
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