Post reply

cannot drop a disabled trigger

  • December 12, 2007 at 3:38 pm

    #70531

    Hi,

    I was trying to drop a disabled trigger, but could not, because it is always deadlocked by other processes. The trigger is on a very busy table. But since its disabled, it should not be fired actively, why could not I drop it?

    Thanks for any insight!

    Kathleen

  • December 12, 2007 at 9:24 pm

    #759671

    What is the error? I'm surprised it doesn't drop.

    Is it a problem if it's disabled?

    You can always start a transaction, enable the trigger, drop it, commit the transaction.

  • December 13, 2007 at 7:17 am

    #759816

    The error was, 'Transaction (Process ID 893) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. '. Since I could not drop it in query window, I scheduled a job to to run every minute to try to drop it, I got the same error. We had to do iisreset to be able to drop it. In fact, it had been a battle as well when I disabled it, which I understood, because the table where the trigger is created on, is the busiest table and every second the table being updated, the trigger is fired, so its competing with existing transactions. But I dont get it why its still deadlocked when its already disabled.

    Thank you for your help.

    Kathleen

  • December 13, 2007 at 8:01 am

    #759839

    I can only guess that it gets a schema lock on the trigger, tries to get one on the table, but another process has gotten a lock on the table, needs to check the trigger to see if it's enabled, and you get a deadlock.

  • December 13, 2007 at 8:14 am

    #759850

    Thanks, Steve, I think it makes sense.

    We kept the trigger disabled for a almost a day, and in the meantime, we exprienced some slowness, do you think the disabled trigger could contribute to the slowness?

    Thanks,

    Kathleen

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply