Nucleus Datakonsult RandWait - pause random number of minutes or seconds

Home

About me and Nucleus
Partners

Articles, tips etc.
My blog
Links
Microsoft articles
Forums

Courses and training




Microsoft Most Valuable Professional


Overview
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.

Details
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.