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 events. Show all posts
Showing posts with label events. Show all posts
Wednesday, March 21, 2012
Monday, March 12, 2012
Scheduled events along with regular events
Hi!
Does it allowable to use scheduled events and regular events together in one subscription class? I get the error when I use then simultaneously. The description of error is
New request is not allowed to start because it should come with valid transaction descriptor.
It is being generated when the rule tries to run.
If I understand your question, you want to know if one subscription class can have event-driven subscriptions and scheduled-subscriptions. Is that right?
The answer is yes. When you define the Subscription class in the ADF, make sure you specify an EventRule and a ScheduledRule node, complete with the appropriate matching rules for each scenario.
HTH....
Subscribe to:
Posts (Atom)