Our new business plan for private Q&A offers single sign-on and advanced features.

Detailed answers to any questions you might have

Discuss the workings and policies of this site

Learn more about hiring developers or posting ads with us

By using our site, you acknowledge that you have read and understand ourCookie PolicyPrivacy Policy, and ourTerms of Service.

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 nversation_endpoints , i do see it and it is marked as is_system = 1 (STARTED_OUTBOUND).

Ive 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,

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.

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

By clicking Post Your Answer, you agree to ourterms of serviceprivacy policyandcookie policy

Unicorn Meta Zoo 2: What is the role of moderators?

How can dragons propel their breath attacks to a long distance

What to do if SUS scores contradict qualitative feedback?

Why does the Earth follow an elliptical trajectory rather than a parabolic one?

What is the best way for a skeleton to impersonate human without using magic?

Are there any established rules for splitting books into parts, chapters, sections etc?

What is the limit on how high you can fly up?

Determine if a string only contains repetitions of a substring

Automatically anti-predictably assemble an alliterative aria

Why was Endgame Thanos so different than Infinity War Thanos?

Why does my circuit work on a breadboard, but not on a perfboard? I am new to soldering

Was this characters old age look CGI or make-up?

On studying Computer Science vs. Software Engineering to become a proficient coder

On what legal basis did the UK remove the European Union from its passport?

How do employ (prime) in math mode at the correct depth?

Why was Thor doubtful about his worthiness to Mjolnir?

site design / logo 2019 Stack Exchange Inc; user contributions licensed undercc by-sa 3.0withattribution required.rev2019.5.12.33663