Asynchronous Service Broker used for Auditing.
1 Should the initiator code sit within the trigger.(Trigger fires after update/insert on table).
2. Data for XML is taken directly from the inserted table. Maybe the reason why the initiator is in the Trigger.
3 Fire and forget setup, End CONVERSATION is first kicked off in the initiator.
4 Target Ends Conversation with Clean up. I guess previously there were problems with sys.conversation_endpoints filling up. Now tempdb is filling up as endpoints/TO are being flushed to the TEMPDB.
5.How to write a synchronous SSSB so it doesn’t affect tempdb.