This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
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.


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:

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.

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'


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