Re: server side trace not working

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

The way you have it configured it will stop at '2005-10-06 10:00:00.000' or
when the file gets to be 50MB in size. YOu should set the date and time to
be 9:00AM the next day. If you specify a 2 instead of 0 for the second
option you will roll over with each 50MB.



--
Andrew J. Kelly SQL MVP


<quackhandle1975@xxxxxxxxxxx> wrote in message
news:1128617243.640218.129270@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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 itself runs with no 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
>


.

Relevant Pages

  • Re: Profiler Bug viewing trace flat file?
    ... Kalen Delaney, SQL Server MVP ... Run this code to create a trace capture of the RPC:Complete event: ... declare @error INT ... Now open the trace file in Profiler and you will see that the Textdata ...
    (microsoft.public.sqlserver.tools)
  • SQL SERVER 2000 domain changes for users.
    ... In the SQL server, we have ... how to change users login name in sql 2000 SP4 after domain change... ... DECLARE @charvalue varchar ... -- temporary srvrole access table ...
    (microsoft.public.sqlserver.server)
  • Re: User and Login auditing
    ... I agree 100% with Sue on not touching the system tables. ... > and hope that your employer doesn't mind you putting the SQL ... > Server box in this position. ... You could put triggers on your trace table. ...
    (microsoft.public.sqlserver.security)
  • Re: Problem with SQL Server [Solution]
    ... Apparently my sql server was not set to mixed mode authentication. ... > with the Trace Properties. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Profiler Bug viewing trace flat file?
    ... I tested this on SQL 2005. ... problem as all the trace files get loaded into SQL through ... I guess you are correct in that fn_trace_gettable may derive the Textdata ... declare @TraceID int ...
    (microsoft.public.sqlserver.tools)