Removal from sys.transmission_queues on exceeding LIFETIME

Hello,

We needed to detach a database that contained a target ServiceBroker service and wanted to do this without impacting the rest of our system. The idea was the ServiceBroker would enqueue message to this service to be pickedup when the database was re-attached. Our sequence to do this was:

1. Disable the queue on the target service forcing ServiceBroker to queue in the initating side transmission queue.

2. Detach the database.

3. Re-attach the database.

4. Re-enable the queue

Messages sent to the service have a specified LIFETIME. What appeared to happen was that messages were being stored in the transmission queue as expected but on hiting the LIFETIME period they were being removed from the transmission queue and were therfore "lost".

Has anyone else experienced this or can anyone suggest what we are doing wrong.

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Any suggestions gratefully recieved.

We have now removed the LIFETIME property from our dialogs in order to reduce the risk of this happening and it all seems fine. My only concern now is that the LIFETIME EXCEEDED message is a Broker error, so does this mean that the same will hold true for all Broker errors that are generated whilst a message is in the transmission queue?|||

Prior to SP2 when receiving an Error or EndDialog message the sys.transmission_queue was drained for the that dialog. The idea was that applications that needed to look at the messages sent in case of error whould use queue RETENTION = ON. With SP2 we introduced a change in which the messages stay in sys.transmission_queue but ar enot being attempted to delivery and they are deleted only after the applications issues the END CONVERSATION.

So yes, any error message will cause the behavior your observed. Either use retention to save the messages sent or upgrade to SP2.

|||

Ian Mitchen wrote:

Incidentally, beaware that detaching a database will disable ServiceBroker in that database when it is re-attached - we discovered that one the hard way :-)

Also restore of a backup will disable the broker. SSB is intedend primarily for distributed applications and one has to consider the problems that can happen when only one side of the conversation is moved (i.e. database is detached/attached): routes have to be fixed, endpoint security has to be set up again for the new host etc. When a backup is restored, the situation is more dramatical, as the peer might be in a more advanced stage of a conversation (i.e. restorer db has next sequence number on a conversation N, but peer is already expecting N+2). In this case the only solution is to error the conversation.

For these considerations the restore and attach operations disable the service broker in the database. One has to inspect the database, ensure is in a consistent state with it's peers and then enable it.

|||

Thanks for a great response, that's filled another gap in my knowledge.

Cheers,

Ian

sql