How do I flush the PRINT buffer in TSQL?

Question

I have a very long-running stored procedure in SQL Server 2005 that I'm trying to debug, and I'm using the 'print' command to do it. The problem is, I'm only getting the messages back from SQL Server at the very end of my sproc - I'd like to be able to flush the message buffer and see these messages immediately during the sproc's runtime, rather than at the very end.

Solution 1

Use the RAISERROR function:

RAISERROR( 'This message will show up right away...',0,1) WITH NOWAIT

You shouldn't completely replace all your prints with raiserror. If you have a loop or large cursor somewhere just do it once or twice per iteration or even just every several iterations.

Also: I first learned about RAISERROR at this link, which I now consider the definitive source on SQL Server Error handling and definitely worth a read:
http://www.sommarskog.se/error-handling-I.html

Solution 2

Building on the answer by @JoelCoehoorn, my approach is to leave all my PRINT statements in place, and simply follow them with the RAISERROR statement to cause the flush.

For example:

PRINT 'MyVariableName: ' + @MyVariableName
RAISERROR(N'', 0, 1) WITH NOWAIT

The advantage of this approach is that the PRINT statements can concatenate strings, whereas the RAISERROR cannot. (So either way you have the same number of lines of code, as you'd have to declare and set a variable to use in RAISERROR).

If, like me, you use AutoHotKey or SSMSBoost or an equivalent tool, you can easily set up a shortcut such as "]flush" to enter the RAISERROR line for you. This saves you time if it is the same line of code every time, i.e. does not need to be customised to hold specific text or a variable.

Solution 3

Yes... The first parameter of the RAISERROR function needs an NVARCHAR variable. So try the following;

-- Replace PRINT function
DECLARE @strMsg NVARCHAR(100)
SELECT @strMsg = 'Here''s your message...'
RAISERROR (@strMsg, 0, 1) WITH NOWAIT

OR

RAISERROR (n'Here''s your message...', 0, 1) WITH NOWAIT

Solution 4

Another better option is to not depend on PRINT or RAISERROR and just load your "print" statements into a ##Temp table in TempDB or a permanent table in your database which will give you visibility to the data immediately via a SELECT statement from another window. This works the best for me. Using a permanent table then also serves as a log to what happened in the past. The print statements are handy for errors, but using the log table you can also determine the exact point of failure based on the last logged value for that particular execution (assuming you track the overall execution start time in your log table.)

Solution 5

Just for the reference, if you work in scripts (batch processing), not in stored procedure, flushing output is triggered by the GO command, e.g.

print 'test'
print 'test'
go

In general, my conclusion is following: output of mssql script execution, executing in SMS GUI or with sqlcmd.exe, is flushed to file, stdoutput, gui window on first GO statement or until the end of the script.

Flushing inside of stored procedure functions differently, since you can not place GO inside.

Reference: tsql Go statement

View more solutions

Comments

[Tomasz Gandor]

Just a short notice for people who'll (like me) think the answers don't work for them: be sure to switch to "Messages" tab when the query is running. By default you'll see the "Results" tab.

[Maury Markowitz]

I'm on Messages and still get nothing.

[Mehmet Ergut]

Look at the Messages tab on the bottom, next to Results tab or switch to Results To Text mode.

[irag10]

Note that TRY/CATCH in SQL will only catch errors with severity > 10, so using RAISERROR in this way won't jump into your CATCH statement. Which is great, as it means you can still use RAISERROR like this with TRY/CATCH. ref: msdn.microsoft.com/en-us/library/ms175976.aspx

[GendoIkari]

Note that this doesn't work after the first 500 messages; once you print more than that it suddenly starts buffering!

[asontu]

go doesn't just flush output, it ends the batch as per the link you provided. Anything you declared is discarded, so not very usable for debugging. declare @test int print "I want to read this!" go set @test=5 will though you an error claiming @test is undefined because it is in a new batch.

[Robert Lujo]

I agree, this is not proper answer to this question, but I put the answer (see disclaimer on the start) since it could be useful for someone else - e.g. someone who runs batch sql.

[GendoIkari]

@MahmoudMoravej No, I'm still running long-running processes using RAISEERROR, and just dealing with the fact that after a while, messages start getting buffered. It appears the only solution would be to use a different tool other than SSMS.

[Joel Coehoorn]

I think this is something that changed in a recent version of SS. Way back when I first wrote this we used RAISERROR for extensive logging of overnight batch processes with many more than 500 messages, and it wasn't a problem. But a lot can change in 7 years.

[Lanorkin]

Some more details in this q stackoverflow.com/questions/20608989/… why this won't work in sqlcmd in recent versions

[SteveJ]

This might be an issue if you are writing a truly transactional script with commit and rollback. I don't believe you will be able to query your temp table live - and it will go away if your transaction fails.

[TheConstructor]

@SteveJ you can query it live by using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; in your monitoring session

[SteveJ]

@TheConstructor ; That is a helpful tip - I'll make use of that, thanks. However, aren't we still left with the temp table going away on rollback? If doing failure analysis, it seems like that would be a big shortcoming.

[TheConstructor]

@SteveJ yes, there is certainly this. You can of course copy the data in a READ UNCOMMITTED transaction to another table, but you probably miss the moment just before ROLLBACK. So it probably solves the 'how far?' not the 'why rollback?'

[Adam]

To switch to Results to Text mode, in SSMS, menu Tools -> Options -> Query Results -> SQL Server -> General -> Default Destination for Results, and choose "Results to Text" instead of "Results to Grids", re-open the query window and then you won't sit there looking at a blank Results tab like a dummy while the RAISERROR output goes to the Messages tab.

[Zartag]

At @GendoIkari's notice. I've tried it with ssms from 2016SP1 with this script. At 500 it switches to buffering 50 lines and at 1k it switches to 100 lines each. This continued at least until 2k, but then I stopped the script. declare @i int set @i = 0 declare @t varchar(100) while 1=1 begin set @i = @i + 1 set @t = 'print ' + convert(varchar, @i) RAISERROR (@t, 10, 1) WITH NOWAIT waitfor delay '00:00:00.010' end

[binki]

Note that RAISERROR() does support printf()-style string interpolation. For example, if @MyVariableName is a stringish type (e.g., VARCHAR(MAX), NVARCHAR(MAX), etc.), you can use RAISERROR() with one line: RAISERROR(N'MyVariableName: %s', 0, 1, @MyVariableName).

[Tomasz Gandor]

This is so convenient! I know that RAISERROR can do some simple substitution, but try substituting a [date]time, or calling a function from inside the RAISERROR statement! This answer gives you a simple FLUSH in the form of raising empty error (at the cost of a newline).

[sisisisi]

With most errors, if you SET XACT_ABORT OFF; and rollback manually in a catch block or by other error detection means, you can save your logs from rollback via a table variable (be sure to use a table var as they are not affected by transaction rollbacks, but temp tables are): ` -- at beginning DECLARE @maxLogId INT = (SELECT MAX(ID) FROM LogTable); -- do stuff -- error handling DECLARE @tmpLog TABLE (/* log table cols */); INSERT INTO @tmpLog SELECT * FROM LogTable WHERE ID > @maxLogId; ROLLBACK TRAN; -- set identity insert on and reinsert contents of tmpLog `