0

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,

Nagaraj S
  • 13,316
  • 6
  • 32
  • 53
Doron
  • 21
  • 3
  • 6
  • Read http://rusanu.com/2007/11/10/when-it-rains-it-pours/ – Remus Rusanu Feb 08 '14 at 07:08
  • Read that article a couple of times. non of the scenarios described match mine. i did a couple of tests and whenever i do iis application pool restart, it immediately start to send thousands of these messages to my error log every couple of milliseconds. setting NEW_BROKER does reset it but i want to find the problem and not fix it like this. i want to try to work with predefined service , queue and sp instead of letting sqldependency create them but that will damage a bit the flexibility of creating new instances without some manual work. – Doron Feb 09 '14 at 07:43

1 Answers1

0

It seems you just need to turn on the trace flag 4133, take a look to this post:

https://dba.stackexchange.com/questions/33750/error-9245-severity-16-state-1-during-the-last-time-interval-xxx-query-n

Community
  • 1
  • 1
Victor Barajas
  • 193
  • 1
  • 9