Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

sql2005 Server side Trace Expand / Collapse
Posted Thursday, December 11, 2008 6:52 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Sunday, August 9, 2009 4:41 AM
Points: 41, Visits: 112
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 '.
Post #617856
Posted Tuesday, July 27, 2010 12:22 PM
Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Wednesday, October 1, 2014 2:26 PM
Points: 691, Visits: 744
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.
Post #959611
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse