May 20, 2008 at 5:04 am
Hi, am trying to create a job in sqlserver 05 to run a profiler trace at intervals of like every 2 minutes, to stop and then start again. Can I run a stored proc to run that trace in the job? Any ideas how to go about it.
May 20, 2008 at 5:15 am
Sure. You can get profiler to create you a script that defines the trace.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 5:19 am
I have scripted the trace, now the challenge is to run a job, setting the trace to stop, then restart, the trace will definitely not enter the job step, so might need a stored procedure, any clues of how I could do that
May 20, 2008 at 5:34 am
What do you mean, "The trace won't enter the job step"?
Set the trace definition once, thenset the job to change the status of the trace, start and pause it. Should do what you want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 20, 2008 at 6:19 am
Here's a stored procedure I use. I removed the actual trace definition to keep it a bit smaller.
CREATE PROCEDURE PerfTrace
@duration int = 60 -- default run the trace for 60 seconds
AS
declare @rc int
declare @TraceID int
declare @DateTime datetime
declare @maxfilesize bigint -- in Mb
DECLARE @FileName nvarchar(256)
, @StartDT datetime -- When the trace started
SET @maxfilesize = 10
SELECT @StartDT = getdate()
-- Set the time to end the trace
SELECT @DateTime = DATEADD (s, @duration, @StartDT)
--IMPORTANT: MAKE SSURE THE PATH EXISTS ON THE SERVER !!!!
SELECT @FileName = 'C:\Temp\Trace_'+ REPLACE (REPLACE (CONVERT(varchar(25),@DateTime),' ','_'),':','-')+'.trc'
-- 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, 2, @FileName,@maxfilesize , @DateTime
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- PLACE here you trace definition scripted from Profiler
-- display trace id for future references
select TraceID=@TraceID
goto finish
error:
select ErrorCode=@rc
finish:
go
[font="Verdana"]Markus Bohse[/font]
May 20, 2008 at 6:29 am
You set a stop time as part of the trace definition. That will stop the trace automatically for you.
From the BOL:
sp_trace_create [ @traceid = ] trace_id OUTPUT
, [ @options = ] option_value
, [ @tracefile = ] 'trace_file'
[ , [ @maxfilesize = ] max_file_size ]
[ , [ @stoptime = ] 'stop_time' ]
[ , [ @filecount = ] 'max_rollover_files' ]
Just set the @stoptime to a value two minutes greater than the current time and you should be good to go.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
May 20, 2008 at 8:00 am
Please what will the value 'OUTPUT' be?
May 20, 2008 at 8:53 am
fosco (5/20/2008)
Please what will the value 'OUTPUT' be?
Not sure if I understand you're question.
The stored procedure I provided will write the trace to one or more files. Other than that what do you mean by OUTPUT value?
[font="Verdana"]Markus Bohse[/font]
May 20, 2008 at 9:07 am
The OUTPUT is an identifier for the trace you just created. You need to capture it as part of the execution, but you don't need to keep it unless you have more need to work with it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply