WAITFOR DELAY and PRINT statement

Question

I have a simple while loop:

DECLARE @Counter INT 

SET @Counter = 1

WHILE (@Counter <= 10)

BEGIN

    PRINT 'The counter value is = ' + CONVERT(VARCHAR,@Counter) WAITFOR DELAY '00:00:01'
    SET @Counter  = @Counter  + 1
    
END

I expect that @counter value will be printed out with 1 second delay, but instead, code is executed after 10 seconds and result is printed at the end of the execution in one go.

The counter value is = 1
The counter value is = 2
The counter value is = 3
The counter value is = 4
The counter value is = 5
The counter value is = 6
The counter value is = 7
The counter value is = 8
The counter value is = 9
The counter value is = 10

How to make sure that every next value is printed out with 1 second delay, so I can monitor progress?

Just some backgroud. I would like to use similar script to monitor backup and restore progress. E.g. assign initial value of progress percentage to:

SELECT @Counter = percent_complete
FROM sys.dm_exec_requests r 
   CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) a 
WHERE r.command in ('BACKUP DATABASE','RESTORE DATABASE');

and monitor progress in while loop every 5 seconds till percent_complete reaches 100%.

_"How to make sure that every next value is printed out with 1 second delay, so I can monitor progress?"_ - **you can't**: Instead [you should use `RAISERROR`](https://learn.microsoft.com/en-us/sql/t-sql/language-elements/raiserror-transact-sql) (sic) with `NOWAIT` instead of `WAITFOR` - that way your client will get the status messages instantly. Also, `WAITFOR` is a separate statement entirely, rather than a modifier for `PRINT`. — Dai, Jul 11 '23 at 10:58
_"Just some backgroud. I would like to use similar script to monitor backup and restore progress. E.g. assign initial value of progress percentage to:"_ - this is not the best approach (imo) for passing backup/restore progress information to a TDS client: [consider using XEvents instead](https://techcommunity.microsoft.com/t5/sql-server-blog/new-extended-events-for-database-recovery-progress/ba-p/384452). — Dai, Jul 11 '23 at 11:01
You can't report *backup* progress like this. The actual progress is already sent to the console anyway. I'd suggest using Ola Hallengren's [backup and restore scripts](https://ola.hallengren.com/sql-server-backup.html) instead of rolling your own anyway. The scripts handle multiple databases and logging operations to a table. If you check the scripts you'll see that `SELECT` is used instead of `PRINT` to return results to the caller immediately. — Panagiotis Kanavos, Jul 11 '23 at 11:43
@Dai Could just use `WITH STATS = 1` and pick it up off the Messages — Charlieface, Jul 11 '23 at 13:35

Stu · Answer 1 · 2023-07-11T12:29:14.980

1

Aside from the advice already in the comments, try this modified script to demo how you can use raiserror for the behaviour you are expecting in your example code.

Remember to select the Messages table to view output.

declare @Counter Int = 1;

while (@Counter <= 10)
begin
  raiserror ('The counter value is %d', 0, 1, @Counter) with nowait;
  waitfor delay '00:00:01';
  set @Counter += 1;
end;

edited Jul 11 '23 at 12:29

answered Jul 11 '23 at 12:21

Stu

30,392
6
14
33

Thank you for this example. It works fine from SQL Server Management Studio, but not from `SQLCMD`. It executes script in background for 10 seconds and when finished shows in output - all lines at the same time. `The counter value is 1 <...> The counter value is 10` – Tax Max Jul 11 '23 at 15:21
1

I don't believe sqlcmd processes the output in the same way. However MS also released an updated version called [Go-sqlcmd](https://github.com/microsoft/go-sqlcmd), I haven't used it but you could try it. – Stu Jul 11 '23 at 16:27
Thanks for this! Didn't know it exists. I'll give a try. – Tax Max Jul 13 '23 at 05:32

score 0 · Answer 2 · answered Jul 11 '23 at 13:09

0

You say

I would like to use similar script to monitor backup and restore progress

Don't use this loop at all. Just use the STATS option, where you can specify a percentage change to show an update. This is what all backup tools use to monitor progress.

BACKUP DATABASE
......
WITH STATS = 1;

answered Jul 11 '23 at 13:09

Charlieface

52,284
6
19
43

Thank you. In my case I have STATS option specified, but percentage is shown at the end of the backup, but not when backup operation is ongoing. sqlcmd -SDB_HOST -Q "BACKUP DATABASE [MY_DB] TO DISK = 'C:\TEMP\MY_DB.bak' WITH FORMAT, STATS = 10;" 10 percent processed. 20 percent processed. <...> 90 percent processed. Processed 466152 pages for database 'MY_DB', file 'MY_DB_data' on file 1. 100 percent processed. – Tax Max Jul 11 '23 at 15:09
Could be sqlcmd doesn't display it until the command is finished. Try in SSMS it should work. – Charlieface Jul 11 '23 at 15:31
It seems you are right. Just found an answer stating "I believe SQLCMD was rewritten for 2012 in such a way that print statements and even raiserror messages issued during a run via SQLCMD never displayed until the entire job finished." – Tax Max Jul 11 '23 at 15:35
What happens if you use the `-j` option – Charlieface Jul 11 '23 at 15:40
Result is shown only at the very end with additional information: `[Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The counter value is 1 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The counter value is 2 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The counter value is 3 [Microsoft][ODBC Driver 13 for SQL Server][SQL Server]The counter value is 4` – Tax Max Jul 11 '23 at 15:51

score 0 · Answer 3 · answered Jul 11 '23 at 15:48

One solution is mentioned in How to show progress in a batch file using sqlcmd?. Other solution is to use Powershell.

GO
CREATE PROCEDURE [scutility].[SqlCmdTestOutput]
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @count INT = 50
    DECLARE @msg VARCHAR(8000)

    WHILE @count > 0
    BEGIN
        SET @msg = convert(VARCHAR(50), sysdatetime())

        RAISERROR (@msg,0,1) WITH NOWAIT

        WAITFOR DELAY '00:00:10'

        SET @count -= 1
    END
END

And to call this procedure use Invoke-Sqlcmd.

powershell.exe -command "Invoke-Sqlcmd -ServerInstance '.' -Query '[scutility].[SqlCmdTestOutput]' -Database SCUTILITY -Verbose -QueryTimeout 0"

More details here - https://dba.stackexchange.com/questions/222054/how-to-report-stats-on-restore-via-sqlcmd

WAITFOR DELAY and PRINT statement

3 Answers3