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,
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.
TechNet Community Support
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
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?
- Edited by DoronB80 Tuesday, February 25, 2014 6:32 AM
You can find contact information from the URL below and verify whether you can open a support case with your local support .