SQL SERVER – Delay Function – WAITFOR clause – Delay Execution of Commands

Blocks the execution of a batch, stored procedure, or transaction until a specified time or time interval is reached, or a specified statement modifies or returns at least one row. This is very useful. Every day when I restore the database to backup server for reports post processing, I use WAITFOR clause.

While executing the WAITFOR statement, the transaction is running and no other requests can run under the same transaction. If the server is busy, the thread may not be immediately scheduled; therefore, the time delay may be longer than the specified time. WAITFOR can be used with query but not with UDF or cursors. WAITFOR wait till TIMEOUT is reached.

Examples of WAITFOR
----Delay for 20 seconds
WAITFOR DELAY '000:00:20'
SELECT '20 Second Delay'
GO
----Delay till 7 AM
WAITFOR TIME '7:00:00'
SELECT 'Good Morning'
GO

Reference : Pinal Dave (http://blog.SQLAuthority.com) , BOL

About these ads

13 thoughts on “SQL SERVER – Delay Function – WAITFOR clause – Delay Execution of Commands

  1. verry interesting,

    but as I know that this ‘waitfor delay’ will only block for the current session, so the sql server will process other requests for other sessions even if the delay interval doesn’t finish yet.

    can you verify that, please ?
    Thanks,
    Mosab

    Like

  2. First, I want to thank you for your blog, I inadvertently read it more than I think I would, it has been a great help.

    Question for you though; I have a cursor which performs a number of actions on databases (CBCC CheckDB & Shrinkdatabase) as well as backs them up and zips them.

    How would I best use a waitfor command (SQL 2000) to make sure the steps aren’t overlapping or leapfrogging the other? A time based variable may just cause more problems as far as I can see (may be too long or too short), so is this possible or am I barking up the wrong tree?

    Thanks

    Tim M

    Like

  3. Hi,
    I read your blogs and find very usefull in solving the problrms releated to sql queries. My problem is that I have a stored procedure and when I execute it first time it take a lot of time vene up to 5-10 minutes.But second time it execute within 1 minutes. But if I execute some other query after its excution or execute the procedures after an interval it again take a long time to execute. Can u please suggest what I have to do to stop this long interval during execution?

    Like

  4. Hi,

    I am kind-of new to SQL server and having trouble with a stored procedure with a query timeout set to 9secs.

    when i use the following sp,
    CREATE PROCEDURE Sleepy @sleep INT AS
    BEGIN
    DECLARE @now DATETIME;
    DECLARE @end DATETIME;
    SET @now = SYSDATETIME();
    SET @end = DATEADD(S, @sleep, @now);
    WHILE (@now < @end)
    SET @now = SYSDATETIME();
    END;

    i do not see a timeout , even tho' the sleepy is passed 10 secs.the sp completed execution wwithout interruption

    However when i use "WAITFOR DELAY" in sp….
    in 9 secs i get a timeout.

    It is not clear , why in the first case, i am not receiving a timeout whereas the second case throws a query timeout exception as expected.

    Please clarify

    Like

  5. Pingback: SQL SERVER – Weekly Series – Memory Lane – #034 | Journey to SQL Authority with Pinal Dave

  6. Pingback: SQL SERVER – Delay Command in SQL Server – SQL in Sixty Seconds #055 | Journey to SQL Authority with Pinal Dave

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s