لطفا منتظر باشید ...
sp_proc_runner
Starting, stopping, and listing traces using stored procedures is handy, but what if you need to schedule these starts and stops? What if you wanted to trace until a given event on the server occurred or for a specified duration of time? Given how large trace files can become, you may want to schedule the tracing so that it doesn't run at all (or runs with a much slimmer event set) during periods of high system activity. This is what sp_proc_runner was designed to address. Sp_proc_runner runs other code. This other code can be a stored procedure, a T-SQL batchyou name itsp_proc_runner doesn't care. It's a miniature scheduler that can run the code you supply for a given period of time or until a condition on the system becomes true (e.g., a long-running transaction or a blocked spid). Sp_proc_runner can also cycle repetitive commands. In other words, it can stop a running process at given intervals and restart it. It can continue to do this until the total duration you told it to run has elapsed or until a condition you've specified becomes true on the server.
Sp_proc_runner can also manage the files that a command you're running may be producing. You can tell it how many files to keep and where to put them. Assuming the routine you're running cooperates, sp_proc_runner can manage your code's output files automatically. For example, let's say you use sp_proc_runner to run sp_start_trace. At regular intervals you may want to stop the trace and restart it. You can either let sp_start_trace manage these files, or you can let sp_proc_runner. You can tell sp_proc_runner to keep only the last ten trace files and it will do exactly that. It will check as the files are being produced, and while cycling the trace, it will delete older files as necessary to keep under the limit you specified.
I originally built sp_proc_runner to run sp_start_trace, but it can actually run anything. You can specify your own code and a custom stop condition, and sp_proc_runner will take care of the rest. Here's its code (Listing 21-7):
Listing 21-7 sp_proc_runner.
USE master
GO
IF OBJECT_ID('sp_proc_runner') IS NOT NULL
DROP PROC sp_proc_runner
GO
CREATE PROC sp_proc_runner
@StartCmd nvarchar(4000)='/?',
@StartTime char(8)=NULL,
@StopCondition nvarchar(4000)=NULL,
@StopMessage nvarchar(4000)='Stop condition met.',
@IterationTime char(8)=NULL,
@Duration char(8)=NULL,
@StopCmd nvarchar(4000)=NULL,
@PollingInterval char(8)='00:00:10',
@OutputDir sysname=NULL,
@OutputFileMask sysname=NULL,
@NumFiles int=16
/*
Object: sp_proc_runner
Description: Runs a specified TSQL
command batch or stored procedure
repetitively for a specified period of time
Returns: (None)
$Author: Ken Henderson $. Email: khen@khen.com
$Revision: 2.0 $
Example:
EXEC sp_proc_runner @StartCmd=N'EXEC sp_start_trace ',
@StopCondition=N'OBJECT_ID(''tempdb..stoptab'') IS NOT NULL',
@StopMessage=N'Trace stopped', @IterationTime='00:30:00',
@StopCmd=N'EXEC sp_stop_trace ',
@OutputDir='c:\temp',
@OutputFileMask='sp_trace*.trc', @NumFiles=16
EXEC sp_proc_runner
@StartCmd=N'EXEC sp_start_trace ',
@IterationTime='00:30:00',
@Duration='12:00:00',
@StopCmd=N'EXEC sp_stop_trace ',
@OutputDir='c:\temp',
@OutputFileMask='sp_trace*.trc', @NumFiles=10
Created: 1999-04-01. $Modtime: 2000-12-16 $.
*/
AS
SET NOCOUNT ON
IF @StartCmd='/?' GOTO Help
-- Do some minimal param checking
IF COALESCE
(@Duration, @StopCondition)
IS NULL BEGIN
RAISERROR('You must supply
either the @Duration or the @StopCondition
parameter.',16,10)
RETURN -1
END
IF @OutputFileMask='*' BEGIN
RAISERROR('You may not specify
an empty file mask.',16,10)
RETURN -1
END
IF (@OutputDir IS NOT NULL)
AND (@OutputFileMask IS NULL) BEGIN
RAISERROR('You must supply
a file mask when supplying a directory.',16,10)
RETURN -1
END
-- Wait until the start time if there is one
IF @StartTime IS NOT NULL
WAITFOR TIME @StartTime
-- Declare some variables
and assign initial values
DECLARE @Stop int, @i int,
@EndTime datetime, @CurDate datetime,
@CurDateStr varchar(25),
@FName sysname,
@DelCmd varchar(255),
@OutputDirCmd varchar(255),
@SCmd nvarchar(4000),
@IterationDateTime datetime
SET @CurDate=getdate()
SET @EndTime=@CurDate+@Duration
-- @Duration of 00:00:00, perhaps?
SET @Stop=CASE WHEN
@CurDate >= @EndTime THEN 1 ELSE 0 END
SET @i=0
SET
@StopCondition='IF ('+@StopCondition+')
RAISERROR('''+@StopMessage+''',11,1)'
-- If we're going to generate
filenames, delete any old ones
IF @OutputDir IS NOT NULL BEGIN
IF RIGHT(@OutputDir,1)<
>'\' SET @OutputDir=@OutputDir+'\'
SET @DelCmd='DEL '+
@OutputDir+@OutputFileMask
EXEC xp_cmdshell
@DelCmd, no_output -- Delete all files matching the mask
-- Prepare for Dir listing (below)
SET @OutputDirCmd='DIR '+
@OutputDir+@OutputFileMask+' /B /ON'
END
-- Check the stop condition - don't start if it's met
--IF (@Stop<>1) AND
(@StopCondition IS NOT NULL)
-- EXEC @Stop=sp_executesql @StopCondition
WHILE (@Stop=0) BEGIN
-- Gen a filename using the current date and time
IF @OutputDir IS NOT NULL BEGIN
SET @CurDateStr=CONVERT(CHAR(8),getdate(),112) +
REPLACE(CONVERT(varchar(15),getdate(),114),':','')
SET @FName=REPLACE(@OutputFileMask,'*',@CurDateStr)
SET @SCmd=@StartCmd+',
@FileName='''+CAST(@OutputDir+@FName as nvarchar(255))+''''
END ELSE SET @SCmd=@StartCmd
EXEC sp_executesql @SCmd -
- Execute the start command
SET @IterationDateTime=getdate()+
ISNULL(@IterationTime,'23:59:59.999')
WHILE (@Stop=0) AND
(getdate()<@IterationDateTime) BEGIN
IF @PollingInterval IS NOT NULL --
Do polling interval delay
WAITFOR DELAY @PollingInterval
-- Check the duration
SET @Stop=CASE WHEN getdate()
>= @EndTime THEN 1 ELSE 0 END
-- Check the stop condition
IF (@Stop<>1) AND
(@StopCondition IS NOT NULL)
EXEC @Stop=sp_executesql @StopCondition
END
IF @StopCmd IS NOT NULL -
- Execute the stop command if there is one
EXEC sp_executesql @StopCmd
SET @i=@i+1
-- Get rid of extra files
IF (@OutputDir IS NOT NULL)
AND (@i>@NumFiles) BEGIN
CREATE TABLE #files (fname varchar(255) NULL)
INSERT #files
EXEC master..xp_cmdshell
@OutputDirCmd
SELECT TOP 1 @DelCmd='DEL '+
@OutputDir+fname FROM #files WHERE fname IS NOT
NULL ORDER BY fname
IF @@ROWCOUNT<>0
EXEC master..xp_cmdshell @DelCmd, no_output
DROP TABLE #files
END
END
RETURN 0
Help:
/*
Code abridged
*/
RETURN -1
GO
EXEC sp_proc_runner
@StartCmd=N'EXEC sp_start_trace ',
@IterationTime='00:30:00', @Duration='12:00:00',
@StopCmd=N'EXEC sp_stop_trace ',
@OutputDir='C:\TEMP',
@OutputFileMask='sp_trace*.trc', @NumFiles=10
In this example we're using sp_proc_runner to execute sp_start_trace. It will run for a total duration of 12 hours and will stop and restart the trace every 30 minutes. It will store the trace files in C:\TEMP and will name the files sp_trace*.trc, where * is replaced with the current date and time. It will keep a maximum of ten files at any one time and will delete older ones as necessary to stay under the limit. Let's look at another example (Listing 21-8):
Listing 21-8 sp_proc_runner can check for a stop condition on the server.
EXEC sp_proc_runner
@StartCmd=N'EXEC sp_start_trace ',
@StopCondition=N'EXISTS(SELECT * FROM sysprocesses WHERE blocked<>0
and waittime>60000)',
@StopMessage=N'Long-term block detected', @IterationTime='00:30:00',
@StopCmd=N'EXEC sp_stop_trace ',
@OutputDir='c:\temp',@OutputFileMask='sp_trace*.trc', @NumFiles=16
In this example, sp_proc_runner again runs sp_start_trace, but this time it watches for a long-term block on the serverone that lasts at least 60 seconds. When this event occurs, sp_proc_runner stops the trace, displays a message, and terminates:
Server: Msg 50000, Level 11, State 1, Line 1
Long-term block detected
Trace started.
The trace filename is : c:\temp\sp_trace20010704030737887.trc.
Deleted trace queue 1.
The trace output filename is: c:\temp\sp_trace20010704030737887.trc.
Between being able to run code for a given total duration and being able to run until a server-side condition comes true, sp_proc_runner affords a great deal of flexibility in scheduling your own jobs on SQL Server. Remember that you can use it to run any type of T-SQL or stored procedure you wish: You're not limited to running sp_start_trace.
