sp_proc_runner | The Guru[ap]s Guide to SQL Server[tm] Stored Procedures, XML, and HTML

for RuBoard

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.

for RuBoard