SQL Service Broker activated proc writes all print messages into SQL Error Log...

Question

If any print message exists in a Service Broker activated proc, it generates a message (9724) : like  

The activated proc '[dbo].[reply_proc]' running on queue 'test.dbo.InitiatorQueue1DB' output the following: '789'

 the message itself has the severity level of 10 and flag not write to log... This gets me to a serious implications like overpouring ErrorLog with insufficient disk space problem, when running a proc like sp_updatestats on a large database... such a proc generates thousands of singe rows and if it runs on a quite regular basis (several times in hour) that problem becomes an a headache... I can wrap it up to CLR proc and suppress those info messages... but , I'm just wondering if it could be done in a simplier way like change a setting in somewhere I'm not aware of place...

Thanks to everybody's attention...

Denis.

Answer 1

This is a copy of my post on Service Broker Branch:

http://social.technet.microsoft.com/Forums/en-US/sqlservicebroker/thread/67bbc6a0-5883-4cb4-99c6-ff1c45c7b28d

If any print message exists in a Service Broker activated proc, it generates a message (9724) : like    the message itself has the severity level of 10 and flag not write to log... This gets me to a serious implications like overpouring ErrorLog with insufficient disk space problem, when running a proc like sp_updatestats on a large database... such a proc generates thousands of singe rows and if it runs on a quite regular basis (several times in hour) that problem becomes an a headache... I can wrap it up to CLR proc and suppress those info messages... but , I'm just wondering if it could be done in a simplier way like change a setting in somewhere I'm not aware of place...

Thanks to everybody's attention...

Denis.

The activated proc '[dbo].[reply_proc]' running on queue 'test.dbo.InitiatorQueue1DB' output the following: '789'

---

Denis

Answer 2

Hi Denis

Thank you for your question.  

I am trying to involve someone more familiar with this topic for a further look at this issue. Sometime delay might be expected from the job transferring. Your patience is greatly appreciated.  

Thank you for your understanding and support. 

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 3

Hi Denis,

Activation stored procedures where designed to write print statements to the SQL Server errorlogs. Normally the print statements are returned to the client but there is no user interactivitity with activation procedures so it was decided to print to the errorlog. Unfortunately to my knowledge there is not a way to override this behavior or disable it.

There are a few techniques that you can consider:

• Like you indicate wrap a CLR proc or xp_cmdshell for these statements.
• Look for an alternative command that does not use print statements. For example, sp_updatestats will use print statements but Update Statistics does not.

Hope it helps some,

Bill -- Microsoft CTS

Answer 4

Hi Bill,

Is there any trace flag (even undocumented) to disable this behaviour?

---

- Chintak (My Blog)

Answer 5

Hi,

I searched through the code and didnt see any traceflags. They did add a trace flag to reduce QN error messages but I dont see any to specifically disable print statements in activation stored procedures to be written to the SQL error logs.

Bill -- Microsoft CTS.

Answer 6

Hi,

I have gone ahead and filed a design change request for the developement team to review and see if it meets the bar for a future enhancement.

Bill -- Microsoft CTS

Answer 7

Thanks Bill.

---

- Chintak (My Blog)