none
Service broker - conversation handle not found messages in SQL Server error log

    Question

  • Many threads opened on this topic, none seem to have an answer for my problem so i am going to give it a try..

    System : Windows Server 2012 SQL Server 2012 Enterprise edition

    Problem : We are using SQLDependency to enable use of query notification in our application which means it creates its own service , queue and procedure.

    Question 1: Looking in the stored procedure i see it does receive top 0. What does it mean to do top 0?

    Now for the real problem.. Occasionally , the error log is filled with the following messages (to the point when i cant open it) : Query notification delivery could not send message on dialog 'X'.... because of the following error in service broker: 'The conversation handle X is not found. The messages appear with the same conversation_handle or 3 different the most.

    when i query that conversation from sys.conversation_endpoints , i do see it and it is marked as is_system = 1 (STARTED_OUTBOUND).

    I've added an alert on error 9245 to try and identify why it happens when it happens. In the message 'During the last time interval X query notification errors were suppressed' , the X is enormous and more than 10,000. i guess the number is related to retrials because we dont have such a number of notifications.

    At first, i thought it might be related with the 'DialogTimer' message because i noticed that when i end the conversation of that specific message , the messages stop but recently it didnt work so the only think i could do to stop it is set NEW_BROKER.

    it immediately happen when i do recycle to our application pool although i see in our logs it calls the SQLDependency.Stop , i dont see the objects (service,queue and procedure) dropped and the error messages start flooding the error log.

    Please let me know if further details are required. Appreciate your help,

    Thanks

    Doron


    DB

    Thursday, February 06, 2014 9:01 AM

All replies

  • Hi Doron,

    Thank you for your question. I am currently looking into this issue and will give you an update as soon as possible.

    Thank you for your understanding and support.

    If you have any feedback on our support, please click here.

    Regards,


    Elvis Long
    TechNet Community Support

    Monday, February 10, 2014 4:50 AM
    Moderator
  • Thank you. i will be happy to provide any information required to help you investigate. I will keep investigating myself.

    Thanks

    DB


    DB

    Monday, February 10, 2014 5:48 AM
  • Hi Doron,

    I suggest that you can try the statement below if you trust the database administrator.

    ALTER DATABASE YouDatabaseName SET TRUSTWORTHY ON

    If above statement cannot resolve this issue, please consider opening a support case with us. Visit this link to see the various support options that are available to better meet your needs:  http://support.microsoft.com/default.aspx?id=fh;en-us;offerprophone

    Friday, February 21, 2014 10:22 AM
  • Hi,

    Thanks for the reply. From reading about this setting i see a lot of recommendations why it should be kept off. I will try it on our QA environment but i doubt if we will move forward with it to our production. 

    Hope we can figure this out because it is very annoying and our only solution now is to use NEW_BROKER which terminates the messages but does not perform the necessary cleanup and we have to manually delete old queues,services and SPs.

    We are using BizSpark license. can i still open a support case?

    Thanks

    Doron


    DB


    • Edited by DoronB80 Tuesday, February 25, 2014 6:32 AM
    Tuesday, February 25, 2014 6:28 AM
  • Hi Doron,

    You can find contact information from the URL below and verify whether you can open a support case with your local support .

    https://support.microsoft.com/ContactUs/TechnicalSupport

    Tuesday, March 04, 2014 1:42 AM
  • When i press the link i get 'Page not found'...

    DB

    Tuesday, March 04, 2014 6:21 AM
  • Hi Doron,

    If above URL does not work, please find "contact us" in https://support.microsoft.com.

    Tuesday, March 11, 2014 1:21 AM
  • Another update:

    When we set the query notification user to be db_owner, it drops all the objects when the dependency stops in all cases including application pool recycle and does not throw the errors we saw before. cant find any post which states why the user has to be db_owner or what is the permission it is missing so it can work properly.

    These are the permission i granted to the user before db_owner:

    CREATE SCHEMA [QN]
    GO

    ALTER USER [UserQN] WITH DEFAULT_SCHEMA=[QN]
    GO

    ALTER AUTHORIZATION ON SCHEMA::[QN] TO [UserQN]
    GO

    GRANT CREATE PROCEDURE TO [UserQN]
    GO

    GRANT CREATE QUEUE TO [UserQN]
    GO

    GRANT CREATE SERVICE TO [UserQN]
    GO

    GRANT REFERENCES on 
    CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
      to [UserQN] 
    GO

    GRANT VIEW DEFINITION TO [UserQN] 
    GO

    GRANT SELECT to [UserQN] 
    GRANT SUBSCRIBE QUERY NOTIFICATIONS TO [UserQN] 
    GRANT RECEIVE ON QueryNotificationErrorsQueue TO [UserQN] 
    GRANT REFERENCES on 
    CONTRACT::[http://schemas.microsoft.com/SQL/Notifications/PostQueryNotification]
      to [UserQN] 

    Maybe you can advise what permission i am missing? i dont want to keep the user as db_owner.

    Thanks

    Doron


    DB

    Wednesday, March 12, 2014 6:52 AM