SQL Server 2008: Can I temporally pause the execution of asynchronous triggers?

Is there a way to temporally suppres开发者_Go百科s the execution of asynchronous triggers in SQL Server 2008. I have implemented the triggers with a Queue and a Service broker that reads from the queue. Since, I want to execute some heavy queries on this server and those triggers will slow the operation drastically - how can I stop the execution of the triggers, but still they get into the queue in order to execute them later.

Thanks in advance!

The first answer with commenting out the stored procedure and then disabling and enabling the queue should work. I have similar situation – service broker queue behind after insert update trigger. The queue uses stored procedure for activation. I did the following (almost the same as the first answer but I do not comment out the stored procedure body): 1. Deactivate the queue:

alter queue QueueName with activation(status = off);

1. Do the mass updates, the messages will be preserved in the queue.

2. Activate the queue:

alter queue QueueName with activation(status = on);

The queue stored procedure will be executed for each of the preserved messages in the queue.

Here what we came up with:

First: Comment the body of the stored procedure that reads the messages in the queue. Everything else (triggers, queue, etc.) is running as it should run. This way the messages go into the queue, but nobody reads from there

Second: Do the heavy lifting scripts. Now the queue is filling with operations that will be later executed.

Third: Uncomment the body of the stored procedure in its original state

Forth: Perform Disable and after Enable operation on the queue. Now the stored procedure will start to read from the queue. And the changes are applied asynchronously.

Yes, DISABLE TRIGGER and when you're done ENABLE TRIGGER. See http://msdn.microsoft.com/en-us/library/ms189748.aspx for the documentation.

However this means that the triggers will never run for the data you put in. If you eventually want the stuff the triggers do to happen, you'll have to manually make it happen. Be aware that this requires duplicated logic, and therefore it is easy to mess it up. Use appropriate amounts of caution.