Print Status Messages Using RAISERROR WITH NOWAIT

To see progress while a long script runs, use RAISERROR WITH NOWAIT instead of PRINT. RAISERROR with a low severity is only a message. The NOWAIT option tells SQL Server to send it to the client at once. PRINT makes no such promise.

Why RAISERROR WITH NOWAIT Beats PRINT

A deployment script with twenty steps is silent while it works. You add a PRINT after each step to see where it is. The text goes into the connection’s output stream, and SQL Server doesn’t say when the client will show it. A long step can finish before the message about the previous one appears.

RAISERROR WITH NOWAIT closes that gap on the server side. Microsoft documents it as sending the message to the client immediately. In Management Studio, each line should appear in the Messages tab as its step finishes. Some client libraries hold messages until the command ends, so what you see depends on the tool. A small loop stands in for three slow steps.

DECLARE @start datetime2 = SYSDATETIME(), @i int = 1, @secs int;
WHILE @i <= 3
BEGIN
    WAITFOR DELAY '00:00:01';
    SET @secs = DATEDIFF(MILLISECOND, @start, SYSDATETIME()) / 1000;
    RAISERROR(N'Batch %d of 3 finished after %d s', 0, 1, @i, @secs) WITH NOWAIT;
    SET @i += 1;
END;

After the format string come the severity (0), the state (1) and the values for the placeholders. Run it, and each line is sent as its step ends.

The values must be variables or literals. An expression such as DATEDIFF(...) in the argument list is a syntax error. That’s why the script computes @secs first.

Put Values in With Placeholders

The first argument is a format string, like the one in the C language. %d takes a whole number and %s takes text. You don’t join the pieces yourself, and you don’t convert numbers to text. The same call works for a row count, a table name or a step number.

RAISERROR(N'Loaded %d rows into %s', 0, 1, 12, N'dbo.Orders') WITH NOWAIT;

That prints Loaded 12 rows into dbo.Orders. Severity 0 and severity 10 both print as plain text. Severity 11 and above print as an error with a Msg line, so keep progress messages at 10 or lower.

The Percent Sign Trap

A percent sign starts a placeholder. That matters when the text comes from a variable and you pass it as the format string. The next script has a message that contains an ordinary percent sign.

DECLARE @text nvarchar(200) = N'Discount 50% off';
RAISERROR(@text, 0, 1) WITH NOWAIT;

SQL Server reads the percent sign and the space after it as a format specification. It finds no value, so it prints Discount 50(null)ff. There’s no error, only a garbled message. Pass the text as a value instead, and the percent sign survives.

DECLARE @text nvarchar(200) = N'Discount 50% off';
RAISERROR(N'%s', 0, 1, @text) WITH NOWAIT;

This prints Discount 50% off. To get a literal percent sign in your own format string, double it: 100%% done prints as 100% done.

Messages Inside TRY and CATCH

This pattern works inside a TRY block, as long as the severity is 10 or lower. Those messages are informational. They print and the block keeps running. A severity of 11 or higher is an error, so control jumps to the CATCH block.

BEGIN TRY
    RAISERROR(N'step 1 done', 0, 1) WITH NOWAIT;
    RAISERROR(N'step 2 done', 10, 1) WITH NOWAIT;
    RAISERROR(N'step 3 failed', 16, 1);
    RAISERROR(N'step 4 never runs', 0, 1) WITH NOWAIT;
END TRY
BEGIN CATCH
    PRINT N'caught: ' + ERROR_MESSAGE();
END CATCH;

Steps 1 and 2 print as they happen. Step 3 is an error, so the CATCH block prints caught: step 3 failed and step 4 never runs. If your progress messages disappear inside a TRY block, check their severity first.

The Length Limit

A RAISERROR message holds at most 2,047 characters. A longer text is cut to 2,044 characters and three dots. Making the variable varchar(max) doesn’t raise the limit. The next script sends 3,000 characters, and 2,047 come back.

DECLARE @m nvarchar(max) = REPLICATE(CAST(N'x' AS nvarchar(max)), 3000);
RAISERROR(@m, 0, 1) WITH NOWAIT;

Keep status messages short. A step name and a row count are enough. If you need more, write the details to a log table.

Use It in a Batch Job

The most useful place for progress messages is a batch loop. This script fills a temporary table with 25,000 rows and deletes them 10,000 at a time. After each batch it reports the running total. A real cleanup job looks the same, with a real table and a real filter.

SET NOCOUNT ON;
CREATE TABLE #Work (Id int PRIMARY KEY);
INSERT INTO #Work (Id)
SELECT TOP (25000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM sys.all_columns a CROSS JOIN sys.all_columns b;
DECLARE @deleted int = 0, @batch int;
WHILE 1 = 1
BEGIN
    DELETE TOP (10000) FROM #Work;
    SET @batch = @@ROWCOUNT;
    IF @batch = 0 BREAK;
    SET @deleted += @batch;
    RAISERROR(N'Deleted %d rows so far', 0, 1, @deleted) WITH NOWAIT;
END;
DROP TABLE #Work;

On a big table, the same messages show that the job is moving and how far it has come.

When a Message Costs Too Much

You could argue that a message after every step is the whole point. For ten steps it is. Each NOWAIT message is a round trip to the client, though. In a loop over millions of rows, send a message every few thousand rows instead.

THROW is the newer way to raise errors, but it has no NOWAIT option and always uses severity 16. For progress, RAISERROR is still the right tool.

What to Remember

Use RAISERROR WITH NOWAIT with severity 0 for progress. Put values in with %d and %s. Pass text from a variable as a value, never as the format string. Keep each message under 2,047 characters.

When I add status messages to a long script, I put one at the start of each step. I put one at the end with the row count. That tells me where a script stopped.

A silent script is not a safe script, it is a script you can’t see.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.