3

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(<text>, 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
1
  • 6
    To have full control over the message display, you could execute the script with PowerShell using System.Data.SqlClient, handling the InfoMessage event and setting FireInfoMessageEventOnUserErrors on the connection. I don't think there is a way to control the behavior in SSMS or other SQL Server tools.
    – Dan Guzman
    Jul 10 '20 at 12:53
0

To have full control over the message display, you could execute the script with PowerShell using System.Data.SqlClient, handling the InfoMessage event and setting FireInfoMessageEventOnUserErrors on the connection. I don't think there is a way to control the behavior in SSMS or other SQL Server tools. – Dan Guzman yesterday

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.