Hi There
I have sent messages and they are all sitting in the transmission queue with a blank status, why is service broker not trying to send them ? They are no errors in the sql log. BOL says this is blank when it has not tried to send the message ? Service broker is definately activated in the database.
How do i force sql server to send anything in the transmission que ?
I have no idea what is wrong or where to check ?
Thanx
Try to follow the guide steps from https://blogs.msdn.com/remusrusanu/archive/2005/12/20/506221.aspx
HTH,
~ Remus
Hi Remus
No this does nto help, this link refers to transmission queue to troubleshoot, the transmission queue has a blank status so thats a dead end ?
The issue we are having is that we have messages in the transmission queue that wont go to the target, status on the transmission queue is blank, however when we run profiler on the target we can see the message trying to be delievered but we get errors like, corrupt message or message has been delivered or security context cannot be establshed, but these errors are not correct, if we send indentical messages from the same inititator and target they go straight through randomly some of them fail.
How do i clear the message queue manually becuase these messages are never gonna go though !
To clean up individual conversations, use END CONVERSATION ... WITH CLEANUP. To cleanup everything in a database, use ALTER DATABASE ... SET NEW_BROKER (see also http://blogs.msdn.com/remusrusanu/archive/2006/01/30/519685.aspx).
The failures you see are not random. It means you have some settings configured correctly, some configured wrong. Are all these messages comming from same service and targeting the same service? Are these event notifications or query notifications by any chance? Have you been changing certificates or certificates/services ownership (ALTER AUTHORIZATION ON ...)?. For 'security context' problems, the Audit Broker Conversation event always indicate what the problem actually is: http://msdn2.microsoft.com/en-us/library/ms191001.aspx.
Also, when investigating these kind of problems I found helpful to restrict the investigation to one conversation at a time. Use the conversation id value to filter out only the events for the conversation of interest. The conversation id is the same on both locations (initiator and target), the event columns description is in BOL: http://msdn2.microsoft.com/en-us/library/ms191302.aspx.
Are you positive you're seeing 'corrupt message' events? These ones I'd really be interested to see. If you can confirm you're seeing such events, please capture a small sample of the profiler trace with this event in it and send it to me at remus.rusanu at microsoft com .
HTH,
~ Remus
Hi Remus
I appreciate your feedback, however some things ic annot explain for example we will send a message , the froma nd to service are identical as well as the message types contracts etc, we will execute teh sql everyting is 100%, we will execute teh sql again a few seconds later, but this stime it sits in the transmission queue with a blank status , same message same everything. We do not touch certificates or anything.
We get the following error in profiler :
10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to
respond.)
I have checked routes a hundred times, if we send messages between these 2 they are succusful just this one does not want to go through ?
Also it seems the transmission queue retries every minute, how can you force it to retry immediately or change the retry interval of the transmission queue ?
I also get the following error:
64(The specified network name is no longer available.) there are 2 messages ,w ell one is an end concersation i get the 2 errors mentioned, however i can copy paste the exact route and ping it from teh server and everything is ok ?
In otehr words the erros are pretty straight forward however everything i try indicates that there is no issue with that route ?
I get these errors for broker:connection class, i get no eros for broker security.
Also i find it odd that these valid erros do not display in the transmission queue ? The status is just blank in the transmission queue, is this correct ?
If so how do you write logic around this, my sql checks the transmission queue status after i send a message for the status, howeve rin this case the message will not get there but the status is blank so my logic thinks everyhting is ok when it is not ?
Dietz wrote:
I appreciate your feedback, however some things ic annot explain for example we will send a message , the froma nd to service are identical as well as the message types contracts etc, we will execute teh sql everyting is 100%, we will execute teh sql again a few seconds later, but this stime it sits in the transmission queue with a blank status , same message same everything. We do not touch certificates or anything.
Last time I've seen this behavior was because on the target there were two instances of the same service, in two databases. One was the intended target, correctly cobfigured. One was a left over from previous attempts, forgotten and not configured. The first message of a new dialog was seeing both services and load balancing across them. The problem is exposed immedeatly by looking at the database id of the generated profiler event, if is not the expected one then you may have this problem.
HTH,
~ Remus
Also i find it odd that these valid erros do not display in the transmission queue ? The status is just blank in the transmission queue, is this correct ?
The transmission queue can only display errors than happen locally, before the message is sent. Once the message is sent, errors happen on the target. There is an administrative and security boundary between the two sites, so we cannot simply push the error from target to be displayed on the sender.
HTH,
~Remus
HI Remus
Thanx for all the help, that makes sense about the transmission queue.
I have confirmed there is no duplicate service on the target instance, the error on the target side in profiler is that the message was not delievered because the security context could not be established.
We have checked endpoint security certificates are cool, however if a dialog was done with an inocrrect certificate for dialog security importing correct dialog certificates wont help because surely the message is stamped with the original certificate, what would one do in that case ? how would you assign a new certificate to a message sitting in the transmission queue to make sure it goes through ?
Once again thanx for your assistance.
10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to
respond.)
This error indicates most likely an incorrect route. The <TCP://machinename:port> is probably mistyped or pointing to a machine that is no longer active.
Is this on the initiator or on the target?
If on initiator, it would indicate that you probably have two routes for the same service, one correct one incorrect. The conversations are load balanced across the said routes and this is why some get through, some don't (but the transmission_status would display this error in this case)
If this is on target, then you either have two (or more) copies of the service in different databases and one database has this incorrect route, either have two (or more) routes back to the intiator in the target service database.
HTH,
~ Remus
We have checked endpoint security certificates are cool, however if a dialog was done with an inocrrect certificate for dialog security importing correct dialog certificates wont help because surely the message is stamped with the original certificate, what would one do in that case ? how would you assign a new certificate to a message sitting in the transmission queue to make sure it goes through ?
Endpoint certificates problem would manifest as refusal to establish connection. If you can connect and the message is sent, then the problem must be in the dialog certificates.
About enforcing the right certificates, here are the 'rules of the game':
- Only the first message in a conversation (in each direction) needs certificates.
- The certificates used are dynamic, i.e. logically each attempt will try to look up the 'best' certificate and use it.
- The certificates involved are the onwes owned by:
- owner of sending service (must have private key)
- user specified in REMOTE SERVICE BINDING (no private key required)
- The users mentioned above are stamped in the conversation info at BEGIN CONVERSATION time. sys.conversation_endpoints will show the user IDs involved. Changing REMOTE SERVICE BINDING or service owner after the conversation was begin does not change these user IDs (I suspect this might be your case)
- The rules for picking a certificate are in BOL, I believe here http://msdn2.microsoft.com/en-us/library/ms166117.aspx
HTH,
~ Remus
Hi Remus
My apologies i have not been very clear. Let me clarify.
When running profiler on the INITIATOR we get 2 errors in succesion :
Network name cannot be established , followed by
10060(A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to
respond.)
I have checked that only one route exists for this service on the initiator and that it is correct, no mistakes in the route ,<TCP://machinename:port> is 100% correct. I can send messages to this service without problems, except for this one stuck in the transmission queue.
On the TARGET when we run profiler we get the , security context cannot be established error.
Now surely if the errors on the INITIATOR were correct we would not see the error on the target ? If it could not find the network name or route why would we see it appear in the target profiler trace trying to connect but get a security context error ?
I have check there are no duplicate services on the target.
I hate to keep bothering you but i have to figure this out, i am going to re-setup this whole service broker environment BUT in future i MUST know how to trouble shoot these problems so i may as well learn now, i really want to figure this out.
Thank You
