I've setup a service broker for the first time and tested it... Looks good. Everytime a DML operation happens on a specific table; Let's say the Price Table, a trigger calls a stored procedure passing it the inserted and deleted tables as XML parameters which will send the message (containing the 2 XMLs) to the queue.
The activation procedure then processes the queue, and inserts the changes done to the initial table into an audit table... Works Great.
The Problem occurs when the update on the Price table is a massive one. In my case, I tested a Price change update on 105 000 Items. The XML Inserted and Deleted Variables, contain 1.3 million lines each... Which causes major issues: TempDB grows infinitely and the processor goes up to 95% which is understandable... but never seems to complete the operation.
I'm trying to figure out how I can break down the inserted and deleted variables (messages) into smaller XMLs. My questions/Thoughts are: Can I create table variables and pass that in the message instead of XML? If so, how to create variable tables in a trigger? Should I create global unique temp tables and reference them in the message?
Any other suggestion, is welcome. Thank you in advance JG