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
Showing posts with label trace. Show all posts
Showing posts with label trace. Show all posts
Wednesday, March 21, 2012
Tuesday, February 21, 2012
schedule a trace
Can someone share an example of how to schedule a trace? I have seen some stuff online that used xp_trace extended procedures however examples are not detailed enough for me to create a script of my own. So if someone can help me with their knowledge that will be great.
thanks
See SQL Server 2005 Books Online topics:
Scheduling Traces
http://msdn2.microsoft.com/en-us/library/ms187656.aspx
Using SQL Trace
http://msdn2.microsoft.com/en-us/library/ms191443.aspx
Subscribe to:
Posts (Atom)