Sample Header Ad - 728x90

SSMS / SQL-Server delays console output (RAISEERROR WITH NOWAIT)

3 votes
1 answer
600 views
It is well known, that SSMS (or maybe the SQL Server, but I'd guess more the SSMS) delays PRINT messages until several lines (40) are gathered in the buffer, before they are really printed to the message window in SSMS (exception: something else is printed out, e.g. the number of rows affected by an UPDATE or the batch is done). So I usually use RAISEERROR(, 0, 0) WITH NOWAIT to print it out immeadiately. This works well for the first 500 rows, after this SSMS seems to start to buffer them again (50 lines, starting at 1000 lines the buffer seems to increase to 1000). Does anybody knows, how I can prevent this "buffer feature" (e.g. if I manually run the statistic updates by using Ola Hallengreens maintenance solution, I'd prefer to know, what it really does at the moment without having to use sp_whoisactive etc.). PS: You can simulate this behavior by using the following "script"
`
DECLARE @i INT = 0

WHILE @i < 10000
BEGIN
    SET @i += 1
    RAISERROR('Step %i', 0, 0, @i) WITH NOWAIT
    --PRINT @i
    WAITFOR DELAY '00:00:01.0' -- wait 1 second, feel free to decrease
END
`
Asked by Thomas Franz (885 rep)
Jul 10, 2020, 11:52 AM
Last activity: Jul 12, 2020, 12:33 PM