80 million sb conversations

Question

Hello,

in our environment (sql2005), I have discovered that there are upwards of 80 million conversations with "DISCONNECTED_INBOUND" listed.  I started to do end conversation to these, but at this rate I will be doing this for the rest of my natural life.

In researching alternate scenario's I came across three different methods that may remove these conversations:

1. (this one may not actually remove any conversations, but i'm unsure)
ALTER DATABASE TestDB SET DISABLE_BROKER
ALTER DATABASE TestDB SET ENABLE_BROKER

2.  ALTER DATABASE TestDB SET NEW_BROKER WITH ROLLBACK IMMEDIATE

3.  ALTER DATABASE TestDB SET ERROR_BROKER_CONVERSATIONS

The path I am leaning towards is step 3. 

My question is with 80 million rows requiring removal, how long will steps 2 and 3 take?  I understand that running step 3 actually disables SB for the time that the rows are being set to errored.    Will it take an hour? Or will it take a week?  And does erroring the conversations actually remove the conversations (eventually)? 

Thanks

Answer 1

Hi Joe2202,

'DISCONNECTED_INBOUND' means the remote side of the conversation has issued an END CONVERSATION. The conversation remains in this state until the local side of the conversation issues an END CONVERSATION. An application might still receive messages for the conversation. Because the remote side of the conversation has ended the conversation, an application cannot send messages on this conversation. When an application issues an END CONVERSATION, the conversation moves to the CD (Closed) state.

It will cause the memory issue. One resolution is: Short term solution is to run the script below which cleans up these conversation left orphaned in sys.conversation_endpoints

DECLARE @handle UNIQUEIDENTIFIER

DECLARE conv_cur CURSOR FAST_FORWARD FOR SELECT CONVERSATION_HANDLE FROM SYS.CONVERSATION_ENDPOINTS

OPEN conv_cur;

FETCH NEXT FROM conv_cur INTO @handle;

WHILE @@fetch_status = 0

BEGIN

END CONVERSATION @handle WITH CLEANUP

FETCH NEXT FROM conv_cur INTO @handle;

END

CLOSE conv_cur;

DEALLOCATE conv_cur;

GO
 
WARNING: This script will cleanup all the conversations. So please modify the query to delete only conversations with a particular state Ex. DISCONNECT_INBOUND. Also make sure that you stop you application which uses Service Broker and ensure that all existing messages are processed by the Target else you might loose messages which are not yet processed by the Target.

I would like to suggest you to use this way instead of your three methods.

For your question:

My question is with 80 million rows requiring removal, how long will steps 2 and 3 take? I understand that running step 3 actually disables SB for the time that the rows are being set to errored. Will it take an hour? Or will it take a week? And does erroring the conversations actually remove the conversations (eventually)?

Actually for 80 million rows, we need sample to test its speed and cannot only guess. Because different servers have different configuration, it is hard to forecast a accurate time for its running. For your methods, as per my knowledge are also good method to troubleshoot DISCONNECTED_INBOUND issue, ROLLBACK IMMEDIATE closing all existing sessions and rolling back any pending transactions, ERROR_BROKER_CONVERSATIONS errors all existing conversations when two databases that have conversations with each other and one of them has an incident and data loss occurs.

Reference:

http://blogs.msdn.com/b/sqlsakthi/archive/2011/04/04/service-broker-quot-fire-and-forget-scenario-quot-and-memory-allocation-failure.aspx

---

Best Regards,
Iric
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Hi Joe2202,

this scenario might happen when the initiator sends a message and then ends the conversation without considering wether or not the target has received and processed it.

If you're using service broker in other databases on the server then you might consider only ending the conversations with the status DISCONNECTED_INBOUND. You can do that with this script :

DECLARE @handle UNIQUEIDENTIFIER 

DECLARE conv_cur CURSOR FAST_FORWARD FOR SELECT CONVERSATION_HANDLE FROM SYS.CONVERSATION_ENDPOINTS 

WHERE state_desc = 'DISCONNECTED_INBOUND'

OPEN conv_cur; 

FETCH NEXT FROM conv_cur INTO @handle; 

WHILE @@fetch_status = 0 

BEGIN 

END CONVERSATION @handle WITH CLEANUP 

FETCH NEXT FROM conv_cur INTO @handle; 

END 

CLOSE conv_cur; 

DEALLOCATE conv_cur; 

GO

Cheers

Régis

---

Blog

Answer 3

I went and performed the SET NEW_BROKER command, on our TEST db.  

At first I put the DB into SINGLE_USER mode:
ALTER DATABASE TestDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

Then set the new broker:
ALTER DATABASE TestDB SET NEW_BROKER
GO

Then set it back to MULTI_USER.

The process took under 10 seconds.  What is interesting, after leaving the TestDB sit overnight, the application seems to be adding 450,000 additional DISCONNECTED_INBOUND messages in one day.

I am going to setup a daily maintenance job (using a memory table instead of a cursor)  to clean out any messages with DISCONNECTED_INBOUND that is over 7 days old. 

I should also point out, since this went so well in our Test db, I am scheduling this to be performed in our Prod db.  The trick is to makes sure all SB conversations are processed before putting the DB into SINGLE_USER mode.

Answer 4

ALTER DATABASE SET NEW_BROKER changes the identity of your database. i.e. Every time you call this the 'id' by which your database is known to service broker changes. Doing a NEW_BROKER will invalidate any (external) routes into your database and may result in many other problems like conversations getting stuck. So I think you should avoid doing this on a regular basis. The problem is, as was pointed out by Regis, that your application does not seem to properly end one end of the conversations: upon receipt of either a "http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog" or a "http://schemas.microsoft.com/SQL/ServiceBroker/Error" message your application needs to call "end conversation <conversation_handle>" end conversation (msdn). The conversation handle should be the one that the EndDialog or Error message was received on.

---

SQL expert for JF Hillebrand IT BV - The Netherlands.