May 7, 2009 at 2:45 pm
Hello,
I wrote a script to run a trace but the files created are only 128KB. It will not cycle to create additional ones. It only creates one file and stops at 128KB. Here's the script. Any ideas? Thanks for any help!
--== Create/Run trace for 5 minutes. File name is dynamic to include time of file creation.
DECLARE @rc Int,
@trace_id Int,
@Trace_File nvarchar(245),
@max_FileSize bigint,
@servName varchar(100),
@endTime datetime,
@onBit bit
SET @servName = REPLACE(@@serverName, '\', '')
SET @endTime = DATEADD(mi, 5, GETDATE())
SET @trace_File = '\\Server\Chare\Trace\Trace_'+
@servName + '_' +
CONVERT(varchar, DATEPART(yy, GETDATE())) +
CONVERT(varchar, DATEPART(mm, GETDATE())) +
CONVERT(varchar, DATEPART(dd, GETDATE())) +
CONVERT(varchar, DATEPART(hh, GETDATE())) +
CONVERT(varchar, DATEPART(mi, GETDATE())) +
CONVERT(varchar, DATEPART(ss, GETDATE()))
SET @max_FileSize = 5
SET @onBit = 1
--== CREATE TRACE DEFINITION
EXEC @rc = sp_trace_create
@traceid = @trace_id OUTPUT,
@options = 0,
@tracefile = @trace_File,
@maxfilesize = @max_FileSize,
@stoptime = @endTime
IF @rc != 0
BEGIN
SELECT @rc
--RAISERROR('Could not create trace definition.', 16, 1)
RETURN
END
--------------------------------------------------------------------
--== 10 = RPC:COMPLETED
--------------------------------------------------------------------
EXEC sp_trace_setevent
@traceid = @trace_id,
@eventid = 10,
@columnid = 14,
@on = @onBit
EXEC sp_trace_setevent
@traceid = @trace_id ,
@eventid = 10,
@columnid = 26,
@on = @onBit
EXEC sp_trace_setevent
@traceid = @trace_id ,
@eventid = 10,
@columnid = 35,
@on = @onBit
EXEC sp_trace_setevent
@traceid = @trace_id ,
@eventid = 10,
@columnid = 16,
@on = @onBit
EXEC sp_trace_setevent
@traceid = @trace_id ,
@eventid = 10,
@columnid = 17,
@on = @onBit
EXEC sp_trace_setevent
@traceid = @trace_id ,
@eventid = 10,
@columnid = 18,
@on = @onBit
EXEC sp_trace_setevent
@traceid = @trace_id ,
@eventid = 10,
@columnid = 13,
@on = @onBit
EXEC sp_trace_setevent
@traceid = @trace_id ,
@eventid = 10,
@columnid = 12,
@on = @onBit
--== APPLY FILTER
EXEC sp_trace_setfilter
@trace_Id, 10, 0, 7, N'SQL Profiler'
--== START THE TRACE
Print 'Starting Trace'
EXEC sp_trace_setstatus
@traceid = @trace_id,
@status = 1
GO
May 7, 2009 at 2:50 pm
Check sp_trace_create in BOL. A problem I see withyour is options is set to 0, when it needs to be 2 for rollover.
May 7, 2009 at 3:10 pm
Thanks, Lynn. I initially had this set to 2, but same results. The script I built from Profiler actually uses 0, so I tried that next which is why I included it this way on the script above.
Thanks!
May 8, 2009 at 6:40 am
Well, since you are setting the stop time to 5 minutes I would venture to say that you are only collecting 128KB of data and would never fill a 5MB file so you wouldn't need to rollover.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 8, 2009 at 10:49 am
Well, the server processes many records a second. 5 minutes should fill up a 5mb file no problem so a roll_over would probably be expected. The problem is that it stops at 128KB, which I did not specify that limit.
May 8, 2009 at 11:48 am
JuanBob (5/8/2009)
Well, the server processes many records a second. 5 minutes should fill up a 5mb file no problem so a roll_over would probably be expected. The problem is that it stops at 128KB, which I did not specify that limit.
It's certainly easy to find out, increase the run time to 10 minutes or omit the stop time parameter and manually stop it.
How big the file gets depends on how many events and columns you include in trace and any filters you have applied as well as activity.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply