scheduled server side trace - not working!

Hi,
I am looking to schedule a server side trace for a production server.
I need to capture all events on the server from 2AM until 9AM. The
server is automatically rebooted at 12-1AM so I have scripted a trace
and wrapped it in a stored proc. A SQL Job is scheduled for 2AM to run
the trace. When I check the trace file in the morning it doesn't show
the correct data, it shows only an hours worth of data for the
afternoon. The SQL Job runs with non errors.
Here's the file:
---
CREATE PROCEDURE dbo.pr_CCDTRACE
AS
/ ****************************************
************/
/* Created by: SQL Profiler */
/* Date: 05/10/2005 15:48:30 */
/ ****************************************
************/
-- Create a Queue
declare @.rc int
declare @.TraceID int
declare @.maxfilesize bigint
declare @.DateTime datetime
set @.DateTime = '2005-10-06 10:00:00.000'
set @.maxfilesize = 50
exec @.rc = sp_trace_create @.TraceID output, 0, N'C:\CDDTrace_06102005',
@.maxfilesize, @.Datetime
if (@.rc != 0) goto error
declare @.on bit
set @.on = 1
exec sp_trace_setevent @.TraceID, 10, 1, @.on
exec sp_trace_setevent @.TraceID, 10, 6, @.on
exec sp_trace_setevent @.TraceID, 10, 9, @.on
exec sp_trace_setevent @.TraceID, 10, 10, @.on
exec sp_trace_setevent @.TraceID, 10, 11, @.on
exec sp_trace_setevent @.TraceID, 10, 12, @.on
exec sp_trace_setevent @.TraceID, 10, 13, @.on
exec sp_trace_setevent @.TraceID, 10, 14, @.on
exec sp_trace_setevent @.TraceID, 10, 15, @.on
exec sp_trace_setevent @.TraceID, 10, 16, @.on
exec sp_trace_setevent @.TraceID, 10, 17, @.on
exec sp_trace_setevent @.TraceID, 10, 18, @.on
exec sp_trace_setevent @.TraceID, 12, 1, @.on
exec sp_trace_setevent @.TraceID, 12, 6, @.on
exec sp_trace_setevent @.TraceID, 12, 9, @.on
exec sp_trace_setevent @.TraceID, 12, 10, @.on
exec sp_trace_setevent @.TraceID, 12, 11, @.on
exec sp_trace_setevent @.TraceID, 12, 12, @.on
exec sp_trace_setevent @.TraceID, 12, 13, @.on
exec sp_trace_setevent @.TraceID, 12, 14, @.on
exec sp_trace_setevent @.TraceID, 12, 15, @.on
exec sp_trace_setevent @.TraceID, 12, 16, @.on
exec sp_trace_setevent @.TraceID, 12, 17, @.on
exec sp_trace_setevent @.TraceID, 12, 18, @.on
exec sp_trace_setevent @.TraceID, 50, 1, @.on
exec sp_trace_setevent @.TraceID, 50, 6, @.on
exec sp_trace_setevent @.TraceID, 50, 9, @.on
exec sp_trace_setevent @.TraceID, 50, 10, @.on
exec sp_trace_setevent @.TraceID, 50, 11, @.on
exec sp_trace_setevent @.TraceID, 50, 12, @.on
exec sp_trace_setevent @.TraceID, 50, 13, @.on
exec sp_trace_setevent @.TraceID, 50, 14, @.on
exec sp_trace_setevent @.TraceID, 50, 15, @.on
exec sp_trace_setevent @.TraceID, 50, 16, @.on
exec sp_trace_setevent @.TraceID, 50, 17, @.on
exec sp_trace_setevent @.TraceID, 50, 18, @.on
exec sp_trace_setevent @.TraceID, 53, 1, @.on
exec sp_trace_setevent @.TraceID, 53, 6, @.on
exec sp_trace_setevent @.TraceID, 53, 9, @.on
exec sp_trace_setevent @.TraceID, 53, 10, @.on
exec sp_trace_setevent @.TraceID, 53, 11, @.on
exec sp_trace_setevent @.TraceID, 53, 12, @.on
exec sp_trace_setevent @.TraceID, 53, 13, @.on
exec sp_trace_setevent @.TraceID, 53, 14, @.on
exec sp_trace_setevent @.TraceID, 53, 15, @.on
exec sp_trace_setevent @.TraceID, 53, 16, @.on
exec sp_trace_setevent @.TraceID, 53, 17, @.on
exec sp_trace_setevent @.TraceID, 53, 18, @.on
exec sp_trace_setevent @.TraceID, 78, 1, @.on
exec sp_trace_setevent @.TraceID, 78, 6, @.on
exec sp_trace_setevent @.TraceID, 78, 9, @.on
exec sp_trace_setevent @.TraceID, 78, 10, @.on
exec sp_trace_setevent @.TraceID, 78, 11, @.on
exec sp_trace_setevent @.TraceID, 78, 12, @.on
exec sp_trace_setevent @.TraceID, 78, 13, @.on
exec sp_trace_setevent @.TraceID, 78, 14, @.on
exec sp_trace_setevent @.TraceID, 78, 15, @.on
exec sp_trace_setevent @.TraceID, 78, 16, @.on
exec sp_trace_setevent @.TraceID, 78, 17, @.on
exec sp_trace_setevent @.TraceID, 78, 18, @.on
exec sp_trace_setevent @.TraceID, 81, 1, @.on
exec sp_trace_setevent @.TraceID, 81, 6, @.on
exec sp_trace_setevent @.TraceID, 81, 9, @.on
exec sp_trace_setevent @.TraceID, 81, 10, @.on
exec sp_trace_setevent @.TraceID, 81, 11, @.on
exec sp_trace_setevent @.TraceID, 81, 12, @.on
exec sp_trace_setevent @.TraceID, 81, 13, @.on
exec sp_trace_setevent @.TraceID, 81, 14, @.on
exec sp_trace_setevent @.TraceID, 81, 15, @.on
exec sp_trace_setevent @.TraceID, 81, 16, @.on
exec sp_trace_setevent @.TraceID, 81, 17, @.on
exec sp_trace_setevent @.TraceID, 81, 18, @.on
exec sp_trace_setevent @.TraceID, 92, 1, @.on
exec sp_trace_setevent @.TraceID, 92, 6, @.on
exec sp_trace_setevent @.TraceID, 92, 9, @.on
exec sp_trace_setevent @.TraceID, 92, 10, @.on
exec sp_trace_setevent @.TraceID, 92, 11, @.on
exec sp_trace_setevent @.TraceID, 92, 12, @.on
exec sp_trace_setevent @.TraceID, 92, 13, @.on
exec sp_trace_setevent @.TraceID, 92, 14, @.on
exec sp_trace_setevent @.TraceID, 92, 15, @.on
exec sp_trace_setevent @.TraceID, 92, 16, @.on
exec sp_trace_setevent @.TraceID, 92, 17, @.on
exec sp_trace_setevent @.TraceID, 92, 18, @.on
exec sp_trace_setevent @.TraceID, 94, 1, @.on
exec sp_trace_setevent @.TraceID, 94, 6, @.on
exec sp_trace_setevent @.TraceID, 94, 9, @.on
exec sp_trace_setevent @.TraceID, 94, 10, @.on
exec sp_trace_setevent @.TraceID, 94, 11, @.on
exec sp_trace_setevent @.TraceID, 94, 12, @.on
exec sp_trace_setevent @.TraceID, 94, 13, @.on
exec sp_trace_setevent @.TraceID, 94, 14, @.on
exec sp_trace_setevent @.TraceID, 94, 15, @.on
exec sp_trace_setevent @.TraceID, 94, 16, @.on
exec sp_trace_setevent @.TraceID, 94, 17, @.on
exec sp_trace_setevent @.TraceID, 94, 18, @.on
-- Set the Filters
declare @.intfilter int
declare @.bigintfilter bigint
set @.intfilter = 7
exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @.TraceID, 1
-- display trace id for future references
select TraceID=@.TraceID
goto finish
error:
select ErrorCode=@.rc
finish:
GO
---
What am I doing wrong?
TIA
fcYou say that you only have an hours worth of data for the _afternoon_, when
the trace is scheduled to run between 2AM and 10 AM. How does that work? Do
you mean you only have trace data for an hour from 2 AM?
I think that the size for the tracefile you have specified might be to
small. Either specify a larger size than 50 MB, or specify 2 for the options
(2nd parameter) to have the tracefile rollover into a new file when the file
size is exceeded.
Jacco Schalkwijk
SQL Server MVP
"Scott" <quackhandle1975@.yahoo.co.uk> wrote in message
news:1128598736.359959.299190@.o13g2000cwo.googlegroups.com...
> Hi,
> I am looking to schedule a server side trace for a production server.
> I need to capture all events on the server from 2AM until 9AM. The
> server is automatically rebooted at 12-1AM so I have scripted a trace
> and wrapped it in a stored proc. A SQL Job is scheduled for 2AM to run
> the trace. When I check the trace file in the morning it doesn't show
> the correct data, it shows only an hours worth of data for the
> afternoon. The SQL Job runs with non errors.
> Here's the file:
> ---
> CREATE PROCEDURE dbo.pr_CCDTRACE
> AS
> / ****************************************
************/
> /* Created by: SQL Profiler */
> /* Date: 05/10/2005 15:48:30 */
> / ****************************************
************/
>
> -- Create a Queue
> declare @.rc int
> declare @.TraceID int
> declare @.maxfilesize bigint
> declare @.DateTime datetime
> set @.DateTime = '2005-10-06 10:00:00.000'
> set @.maxfilesize = 50
> exec @.rc = sp_trace_create @.TraceID output, 0, N'C:\CDDTrace_06102005',
> @.maxfilesize, @.Datetime
> if (@.rc != 0) goto error
> declare @.on bit
> set @.on = 1
> exec sp_trace_setevent @.TraceID, 10, 1, @.on
> exec sp_trace_setevent @.TraceID, 10, 6, @.on
> exec sp_trace_setevent @.TraceID, 10, 9, @.on
> exec sp_trace_setevent @.TraceID, 10, 10, @.on
> exec sp_trace_setevent @.TraceID, 10, 11, @.on
> exec sp_trace_setevent @.TraceID, 10, 12, @.on
> exec sp_trace_setevent @.TraceID, 10, 13, @.on
> exec sp_trace_setevent @.TraceID, 10, 14, @.on
> exec sp_trace_setevent @.TraceID, 10, 15, @.on
> exec sp_trace_setevent @.TraceID, 10, 16, @.on
> exec sp_trace_setevent @.TraceID, 10, 17, @.on
> exec sp_trace_setevent @.TraceID, 10, 18, @.on
> exec sp_trace_setevent @.TraceID, 12, 1, @.on
> exec sp_trace_setevent @.TraceID, 12, 6, @.on
> exec sp_trace_setevent @.TraceID, 12, 9, @.on
> exec sp_trace_setevent @.TraceID, 12, 10, @.on
> exec sp_trace_setevent @.TraceID, 12, 11, @.on
> exec sp_trace_setevent @.TraceID, 12, 12, @.on
> exec sp_trace_setevent @.TraceID, 12, 13, @.on
> exec sp_trace_setevent @.TraceID, 12, 14, @.on
> exec sp_trace_setevent @.TraceID, 12, 15, @.on
> exec sp_trace_setevent @.TraceID, 12, 16, @.on
> exec sp_trace_setevent @.TraceID, 12, 17, @.on
> exec sp_trace_setevent @.TraceID, 12, 18, @.on
> exec sp_trace_setevent @.TraceID, 50, 1, @.on
> exec sp_trace_setevent @.TraceID, 50, 6, @.on
> exec sp_trace_setevent @.TraceID, 50, 9, @.on
> exec sp_trace_setevent @.TraceID, 50, 10, @.on
> exec sp_trace_setevent @.TraceID, 50, 11, @.on
> exec sp_trace_setevent @.TraceID, 50, 12, @.on
> exec sp_trace_setevent @.TraceID, 50, 13, @.on
> exec sp_trace_setevent @.TraceID, 50, 14, @.on
> exec sp_trace_setevent @.TraceID, 50, 15, @.on
> exec sp_trace_setevent @.TraceID, 50, 16, @.on
> exec sp_trace_setevent @.TraceID, 50, 17, @.on
> exec sp_trace_setevent @.TraceID, 50, 18, @.on
> exec sp_trace_setevent @.TraceID, 53, 1, @.on
> exec sp_trace_setevent @.TraceID, 53, 6, @.on
> exec sp_trace_setevent @.TraceID, 53, 9, @.on
> exec sp_trace_setevent @.TraceID, 53, 10, @.on
> exec sp_trace_setevent @.TraceID, 53, 11, @.on
> exec sp_trace_setevent @.TraceID, 53, 12, @.on
> exec sp_trace_setevent @.TraceID, 53, 13, @.on
> exec sp_trace_setevent @.TraceID, 53, 14, @.on
> exec sp_trace_setevent @.TraceID, 53, 15, @.on
> exec sp_trace_setevent @.TraceID, 53, 16, @.on
> exec sp_trace_setevent @.TraceID, 53, 17, @.on
> exec sp_trace_setevent @.TraceID, 53, 18, @.on
> exec sp_trace_setevent @.TraceID, 78, 1, @.on
> exec sp_trace_setevent @.TraceID, 78, 6, @.on
> exec sp_trace_setevent @.TraceID, 78, 9, @.on
> exec sp_trace_setevent @.TraceID, 78, 10, @.on
> exec sp_trace_setevent @.TraceID, 78, 11, @.on
> exec sp_trace_setevent @.TraceID, 78, 12, @.on
> exec sp_trace_setevent @.TraceID, 78, 13, @.on
> exec sp_trace_setevent @.TraceID, 78, 14, @.on
> exec sp_trace_setevent @.TraceID, 78, 15, @.on
> exec sp_trace_setevent @.TraceID, 78, 16, @.on
> exec sp_trace_setevent @.TraceID, 78, 17, @.on
> exec sp_trace_setevent @.TraceID, 78, 18, @.on
> exec sp_trace_setevent @.TraceID, 81, 1, @.on
> exec sp_trace_setevent @.TraceID, 81, 6, @.on
> exec sp_trace_setevent @.TraceID, 81, 9, @.on
> exec sp_trace_setevent @.TraceID, 81, 10, @.on
> exec sp_trace_setevent @.TraceID, 81, 11, @.on
> exec sp_trace_setevent @.TraceID, 81, 12, @.on
> exec sp_trace_setevent @.TraceID, 81, 13, @.on
> exec sp_trace_setevent @.TraceID, 81, 14, @.on
> exec sp_trace_setevent @.TraceID, 81, 15, @.on
> exec sp_trace_setevent @.TraceID, 81, 16, @.on
> exec sp_trace_setevent @.TraceID, 81, 17, @.on
> exec sp_trace_setevent @.TraceID, 81, 18, @.on
> exec sp_trace_setevent @.TraceID, 92, 1, @.on
> exec sp_trace_setevent @.TraceID, 92, 6, @.on
> exec sp_trace_setevent @.TraceID, 92, 9, @.on
> exec sp_trace_setevent @.TraceID, 92, 10, @.on
> exec sp_trace_setevent @.TraceID, 92, 11, @.on
> exec sp_trace_setevent @.TraceID, 92, 12, @.on
> exec sp_trace_setevent @.TraceID, 92, 13, @.on
> exec sp_trace_setevent @.TraceID, 92, 14, @.on
> exec sp_trace_setevent @.TraceID, 92, 15, @.on
> exec sp_trace_setevent @.TraceID, 92, 16, @.on
> exec sp_trace_setevent @.TraceID, 92, 17, @.on
> exec sp_trace_setevent @.TraceID, 92, 18, @.on
> exec sp_trace_setevent @.TraceID, 94, 1, @.on
> exec sp_trace_setevent @.TraceID, 94, 6, @.on
> exec sp_trace_setevent @.TraceID, 94, 9, @.on
> exec sp_trace_setevent @.TraceID, 94, 10, @.on
> exec sp_trace_setevent @.TraceID, 94, 11, @.on
> exec sp_trace_setevent @.TraceID, 94, 12, @.on
> exec sp_trace_setevent @.TraceID, 94, 13, @.on
> exec sp_trace_setevent @.TraceID, 94, 14, @.on
> exec sp_trace_setevent @.TraceID, 94, 15, @.on
> exec sp_trace_setevent @.TraceID, 94, 16, @.on
> exec sp_trace_setevent @.TraceID, 94, 17, @.on
> exec sp_trace_setevent @.TraceID, 94, 18, @.on
>
> -- Set the Filters
> declare @.intfilter int
> declare @.bigintfilter bigint
> set @.intfilter = 7
> exec sp_trace_setfilter @.TraceID, 3, 1, 0, @.intfilter
> exec sp_trace_setfilter @.TraceID, 10, 0, 7, N'SQL Profiler'
>
> -- Set the trace status to start
> exec sp_trace_setstatus @.TraceID, 1
> -- display trace id for future references
> select TraceID=@.TraceID
> goto finish
> error:
> select ErrorCode=@.rc
> finish:
> GO
> ---
> What am I doing wrong?
> TIA
> fc
>sql