July 29, 2007 at 10:55 pm
I'm trying to disable the trigger that updates a search table which takes part in a push replication. There are bulk operations (insert/update/delete) happening on the table through scheduled tasks and different applications.
Disabling the trigger through the 'Disable Trigger' command or Management Studio on the production database returns the following result:
Alter failed for Trigger 'X'
- Lock request time out period exceeded.
I understand I can use the "Sqlcmd -A" to run the "Disable Trigger" and wait for a long time for it to go through but I'm looking for a method to instantly disable the trigger (remove locking because of the ongoing operations) without interopting the dependent operations or changing the trigger's code like addig an extra condition to bypass the trigger's operation.
Thanks.
July 30, 2007 at 10:40 am
the fastest method is to create a table in the DB with a TriggerXEnabled property and have the trigger code run with "if not exists( select * from configtable where configname = 'TriggerXEnabled' and configvalue = 0) begin [actual triggercode here ]... "
hth
* Noel
July 31, 2007 at 12:43 am
Thanks Noeld for your response and definitly makes sense; the problem at this moment is that this is a very busy production server which for any alteration to the trigger, whole lots of different processes which are hammering the trigger should be turned off or alternatively implement your suggestion or send a disable trigger command, wait for god knows how long, block the incomming traffic to stop the trigger in what's been doing to apply the change or simple disable it.
I was wondering if there are other alternatives.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply