SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


sql2005 Server side Trace


sql2005 Server side Trace

Author
Message
71 camaro
71 camaro
SSC-Addicted
SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)SSC-Addicted (415 reputation)

Group: General Forum Members
Points: 415 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 '.
steve smith-401573
steve smith-401573
SSCarpal Tunnel
SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)SSCarpal Tunnel (5K reputation)

Group: General Forum Members
Points: 4985 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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum







































































































































































SQLServerCentral


Search