Re: SQL Profiler Scheduling
- From: Ron <Ron@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Fri, 17 Aug 2007 10:34:02 -0700
Thank you, that did the trick. I took the trace script and put it in a
stored procedure. Since I want to ovewrite the output trace each day, I
created a step to delete the file before running the trace.
Thanks again everone.
"Kalen Delaney" wrote:
Hi Ron.
Can you create a stored procedure from the script, and then call the
procedure as the job step?
--
HTH
Kalen Delaney, SQL Server MVP
www.InsideSQLServer.com
http://sqlblog.com
"Ron" <Ron@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:ED862D55-0B82-4707-AD83-B2262B412AE4@xxxxxxxxxxxxxxxx
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
- References:
- Re: SQL Profiler Scheduling
- From: Russell Fields
- Re: SQL Profiler Scheduling
- From: Ron
- Re: SQL Profiler Scheduling
- From: Kalen Delaney
- Re: SQL Profiler Scheduling
- Prev by Date: Re: Remote Debugging Problems w/ VS.net 2005 SP1
- Next by Date: DMO and SS 2008
- Previous by thread: Re: SQL Profiler Scheduling
- Next by thread: Re: SQL Profiler Scheduling
- Index(es):
Relevant Pages
|
Loading
