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

« SQL SERVER – De-fragmentation of Database at Operating System to Improve Performance

SQLAuthority.com News – Journey to SQL Authority Milestone of SQL Server »

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

June 18, 2007 by pinaldave

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

Posted in Pinal Dave, SQL, SQL Authority, SQL DateTime, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology | 8 Comments

8 Responses

on January 2, 2008 at 12:37 pm | Reply MoSaB

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
on March 31, 2008 at 9:23 pm | Reply Tim M.

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
on February 14, 2009 at 5:50 am | Reply Rama

Thanks for the code
on May 5, 2009 at 7:12 pm | Reply Dharmender Kumar

Thanks to give this solution. Thankyou so much!!
on September 11, 2009 at 1:17 pm | Reply Anas

Thanks for the enlightenment, it’s fix my problem…
on November 24, 2009 at 6:11 pm | Reply Andik Laksono

Wow , it’s work Man !!!

thanks a lot
on December 23, 2010 at 2:32 pm | Reply Ritish

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?
on April 22, 2011 at 1:35 am | Reply anuradha

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