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

  • 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.

    Tuesday, July 24, 2012 3:04 AM

Answers

  • 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

    Monday, July 30, 2012 3:29 PM

All replies

  • 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

    • Merged by amber zhang Thursday, July 26, 2012 1:55 AM
    Tuesday, July 24, 2012 11:49 PM
  • 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.

    Thursday, July 26, 2012 1:55 AM
    Moderator
  • 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

    Monday, July 30, 2012 3:29 PM
  • Hi Bill,

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


    - Chintak (My Blog)

    Wednesday, August 1, 2012 7:24 AM
  • 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.

    Wednesday, August 1, 2012 2:33 PM
  • 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

    Wednesday, August 1, 2012 2:38 PM
  • Thanks Bill.

    - Chintak (My Blog)

    Monday, August 6, 2012 1:58 PM
  • Any update on this. This is caused by RaisError as well as print statements. And the MSFT product Orchestrator is killing our SQL log on its host server.


    My blog: SQL Soldier
    Twitter: @SQLSoldier
    Microsoft Certified Master: SQL Server 2008
    My book: Pro SQL Server 2008 Mirroring

    Thursday, June 6, 2013 5:04 PM