July 23, 2003 at 8:55 am
I have a long running stored procedure for in which I would like to provide some processing milestones for the DBA.
I'm not able to figure out what is causing the print statements to wait until the entire procedure complete before returning my message text to Query Analyzer. Are there any suggestions for printing status messages from a stored procedure?
I'm using:
print 'My Message'
July 23, 2003 at 11:14 am
You will see the results in QA usually after transactions are committed. If your proc runs in a nested transaction, produces nested transactions, or starts a transaction without any checkpoints, you may not see output for a while. I ran into this issue myself when running a really long batch. As soon as the transaction count exceeded 1, I had to wait until the transactions committed or rolled back to see the results of print statements. I guess the PRINT statements doesn't have a very high priority to the SQL output stream, or perhaps it is dependent on other parts of the batch finishing??? I'm really not sure.
July 23, 2003 at 11:25 am
The proc does have transaction in it, but I'm printing a message before the first "BEGIN TRAN" statement.
Speaking of checkpoints....does anyone know of a DBCC command to force one to occur? I was curious if you can force a database using the Simple recovery model to clean up its transaction log on demmand.
July 23, 2003 at 11:34 am
When I mentioned checkpoints, I was actually referring to "savepoints" in a transaction, created using the SAVE TRANSACTION command. Also, the procedure is executed in a batch. If the batch has a transaction in it, I believe that the PRINT statement will still be affected by transactions inside the batch...I think you'd have to know about the internals of how SQL processes the batch to know what priority it assigns to a simple printf() function. It may internally be keeping a buffer for the output of the batch, and processing for the transaction might delay the output of the buffer. There seems to be some sort of flush buffer method that gets called "every so often", but you'd have to know the internals to know exactly at what intervals or events the flushing occurs.
July 24, 2003 at 2:46 am
brianr - just type CHECKPOINT into QA
I had a similar issue recently so I ended up writing milestone messages to a dummy table rather than using PRINT. I could then query the table (using WITH (NOLOCK) for good measure) so see how far things had progressed
July 24, 2003 at 9:51 am
This has happened to me also. Would a statement like
RAISERROR('FYI: This message should be sent without delay', 10, 1) WITH NOWAIT
work for you? The severity is only informational and @@ERROR will be zero.
RandyHelpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
July 24, 2003 at 10:00 am
rstone--That will work perfectly! Thanks so much, it looks like a few of us will find this very useful.
-Brian
July 24, 2003 at 11:13 am
Great find, rstone!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply