Re: SQL Profiler Scheduling
- From: Ron <Ron@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 15 Aug 2007 12:36:03 -0700
I created a trace, then exported it out, then took the contents of that and
put it into a job step. I'm getting the following error:
create failed for jobstep
string or binary data would be truncated
Here's the job step contents:
/****************************************************/
/* Created by: SQL Server Profiler 2005 */
/* Date: 08/14/2007 02:08:02 PM */
/****************************************************/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 5000
-- Please replace the text InsertFileNameHere, with an appropriate
-- filename prefixed by a path, e.g., c:\MyFolder\MyTrace. The .trc extension
-- will be appended to the filename automatically. If you are writing from
-- remote server to local drive, please use UNC path and make sure server has
-- write access to your network share
exec @rc = sp_trace_create @TraceID output, 0, N'D:\MSSQL\LOG\echotrace',
@maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 15, 15, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 17, 12, @on
exec sp_trace_setevent @TraceID, 17, 1, @on
exec sp_trace_setevent @TraceID, 17, 9, @on
exec sp_trace_setevent @TraceID, 17, 6, @on
exec sp_trace_setevent @TraceID, 17, 10, @on
exec sp_trace_setevent @TraceID, 17, 14, @on
exec sp_trace_setevent @TraceID, 17, 11, @on
exec sp_trace_setevent @TraceID, 10, 15, @on
exec sp_trace_setevent @TraceID, 10, 16, @on
exec sp_trace_setevent @TraceID, 10, 9, @on
exec sp_trace_setevent @TraceID, 10, 17, @on
exec sp_trace_setevent @TraceID, 10, 2, @on
exec sp_trace_setevent @TraceID, 10, 10, @on
exec sp_trace_setevent @TraceID, 10, 18, @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, 6, @on
exec sp_trace_setevent @TraceID, 10, 14, @on
exec sp_trace_setevent @TraceID, 12, 15, @on
exec sp_trace_setevent @TraceID, 12, 16, @on
exec sp_trace_setevent @TraceID, 12, 1, @on
exec sp_trace_setevent @TraceID, 12, 9, @on
exec sp_trace_setevent @TraceID, 12, 17, @on
exec sp_trace_setevent @TraceID, 12, 6, @on
exec sp_trace_setevent @TraceID, 12, 10, @on
exec sp_trace_setevent @TraceID, 12, 14, @on
exec sp_trace_setevent @TraceID, 12, 18, @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, 13, 12, @on
exec sp_trace_setevent @TraceID, 13, 1, @on
exec sp_trace_setevent @TraceID, 13, 9, @on
exec sp_trace_setevent @TraceID, 13, 6, @on
exec sp_trace_setevent @TraceID, 13, 10, @on
exec sp_trace_setevent @TraceID, 13, 14, @on
exec sp_trace_setevent @TraceID, 13, 11, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Server Profiler -
e1d85382-fd0b-421b-a9ce-b93b26867bdf'
-- 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
"Russell Fields" wrote:
Ron,.
Sure, just use a SQL Agent job to start the trace running at 8am. The job
will just run a second or two, then the trace will be started.
Create a trace interactively then save the script and use the script in the
job step. Be sure to set a stop time in the trace definition. E.g.
-- Set a stop time for the trace
set @DateTime = SUBSTRING(CONVERT(char(10),GETDATE(),120),1,10) + '
17:00:00.000'
RLF
"Ron" <Ron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:F827E8B5-274A-4894-9472-39BE7C2A57E3@xxxxxxxxxxxxxxxx
Using SQL2005 Standard Edition SP1
Is there anyway to schedule a profile trace? I would like it start at 8am
and stop at 5pm without manual intervention. It could overwrite itself or
not.
We have occasional slowness that we can't seem to capture, so having a
trace
start each morning would help us when the problem occurred.
Thanks
Ron
- Follow-Ups:
- Re: SQL Profiler Scheduling
- From: Russell Fields
- Re: SQL Profiler Scheduling
- From: Kalen Delaney
- Re: SQL Profiler Scheduling
- References:
- Re: SQL Profiler Scheduling
- From: Russell Fields
- Re: SQL Profiler Scheduling
- Prev by Date: Re: Profiler 2000
- Next by Date: Re: SQL Profiler Scheduling
- Previous by thread: Re: SQL Profiler Scheduling
- Next by thread: Re: SQL Profiler Scheduling
- Index(es):
Relevant Pages
|
