SQL 2005+Transact-SQL Delayed Execution
Transact-SQL includes the WAITFOR statement. This command allows the execution of a script or procedure to be blocked for a period of up to twenty-four hours. The process can be delayed until a specified time or for a number of hours, minutes and seconds.
WAITFOR Command
SQL Server provides several methods to delay the execution of Transact-SQL batches or stored procedures. One of the simplest of these is the WAITFOR command. This command allows you to delay processing for a specified period or until a given time. This command is mostly used for one-off execution rather than for recurring schedules. For example, it is ideal for scheduling a process that you want to run overnight when no users are accessing a database.
Pausing for a Specified Period
If you wish to pause for a specified period before continuing execution, you can use the WAITFOR DELAY variation of the statement. The amount of time to wait is specified as a number of hours, minutes and, optionally, seconds. The maximum delay is twenty-four hours. For example, the following code pauses for ten seconds before outputting a message.
WAITFOR DELAY '00:00:10'
PRINT '10 seconds passed'
If you do not need to specify the number of seconds, this section of the delay can be excluded, as follows:
WAITFOR DELAY '00:02'
PRINT '2 minutes passed'
Waiting Until a Specific Time
You may wish to delay processing until a specified time. To do so, you can use the WAITFOR TIME version of the command. The command requires that a time be specified but a date may not be included. This means that the maximum delay is twenty-four hours. The following sample waits until 1:30am before printing a message.
WAITFOR TIME '01:30'
PRINT 'Executed at 1:30AM'
If you require more accuracy, you can provide a time that includes a number of seconds:
WAITFOR TIME '01:30:45'
PRINT 'Executed at 1:30:45AM'
Limitations
Every active WAITFOR statement uses a thread. You should limit the number of active WAITFORs to ensure that a large number of threads are not blocked. SQL Server can decide to terminate WAITFOR statements if the number of available threads becomes too low.
Care should be taken not to hold locks before a long-running WAITFOR statement is reached. Locked rows can block the execution of other statements until the WAITFOR command completes and may also cause deadlocks.
21 January 2010