Nucleus Datakonsult RandWait - pause random number of minutes or seconds


About me and Nucleus

Articles, tips etc.
My blog
Microsoft articles

Courses and training

Microsoft Most Valuable Professional


Say you have a number of scheduled jobs that all start at the same time, perhaps coming from a master server (MSX). If they use shared resources (virtual machines in same host and/or a common SAN) you might want to spread the load a bit. I usually call a stored procedure as a first job step, and that procedure wait a random number of minutes between 0 and 30. This is the RandWait procedure.

Versions etc.
This script was written and tested on SQL Server 2012. It should work with earlier versions as well.
See comment block in procedure source code for version history of the procedure.

Two parameters:
The first parameter, @sec_or_min, specifies the usit to wait. Allowed values are 's' (seconds) or 'm' (minutes, the default).
The second parameter is the timespan, @span tinyint, with a default of 30. I.e., the proc will paus between 0 and this value.

Usage examples
EXEC RandWait
EXEC RandWait @sec_or_min = 'm', @span = 30
EXEC RandWait @sec_or_min = 's', @span = 10

The code:
You find the code here.