Post reply

deleting triggers caused DB lockout

  • May 8, 2017 at 5:00 pm

    #323409

    We are using SQL 2008 and we had alot of orphaned triggers (due to various reasons).

    We did a bulk delete of these (over 25 of them) and it basically locked up the DB and none of the users could access the DB.

    Is this normal behavior for deleting triggers ?  We are not sure why we locked up the DB and why any user request just sat there with an hour glass.

    Does deleting a trigger require a dedicated DB ??

    thanks
    greg

  • May 8, 2017 at 6:59 pm

    #1941700

    The tables with the triggers would have schema modification locks on them to drop a trigger so there wouldn't be any access to those tables until the locks are released. You could certainly have requests sitting while the deletes took place. Anytime you do any DDL changes, you want to understand the impact it will have on the database, existing code, other database objects and any applications that access that database.
    Many places, most that I've seen, require those types of changes to be done during off hours or maintenance windows. For the reasons you just saw

    Sue

Viewing 2 posts - 1 through 2 (of 2 total)

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