How To Flush PRINT Buffer in T-SQL, Or Output Messages In Real Time

When debugging SQL scripts, oftentimes the developer or DBA would want to see what is happening as the script executes. While it is possible to set up breakpoints in SSMS and step-into/-over code, sometimes all that one wants is to see the progression. The most common method of doing this is to use the PRINT statement. However, this does not really work with scripts that take their time executing. Consider the following script, in which I simulate long running queries by introducing a delay. All the script does is it outputs the numbers from 1 to 5, waiting for one second between outputs:

DECLARE @i INT = 0
PRINT 'Starting...'
WHILE @i < 5
BEGIN
		SET @i = @i + 1
		WAITFOR DELAY '0:00:01'
		PRINT @i
END
PRINT 'Done!'

Now, if you run this script, you'll get the following output:

Starting...
1
2
3
4
5
Done!

The problem is, you will only see this when the script finishes and all the PRINT statements have been flushed – that is, in 5 seconds. Go ahead, try it! – sucks, right? If you want to have your messages appear in real time, what you are looking for is the RAISERROR command. Note that while intended for raising errors, it will only break the execution and go into CATCH block (if there is one) only if the severity provided to the command is greater than 10. So, let's replace the PRINT statements with RAISERROR, fire it up and see how the script outputs numbers in real-time:

DECLARE @i INT = 0
DECLARE @s VARCHAR
RAISERROR('Starting...', 0, 0) WITH NOWAIT
WHILE @i <= 5
BEGIN
		SET @i = @i + 1
		WAITFOR DELAY '0:00:01'
		SET @s = CAST(@i AS VARCHAR)
		RAISERROR(@s, 0, 0) WITH NOWAIT
END
RAISERROR('Done!', 0, 0) WITH NOWAIT

Tweet

a1c9446c55ded0083d5f912e02156b7d