Post reply

cannot alter instead of trigger on production table

  • May 8, 2007 at 10:40 am

    #123698

    Every time I attempt to alter or even drop a trigger on a table on our production database (SQL2000 sp4) it never actually alters or drops it simply blocks and locks all other processes, queries, etc... from using that table.  I keep having to kill the ALTER TRIGGER or DROP TRIGGER attempt.  How can I drop my trigger without kicking everyone out of production?

  • May 8, 2007 at 11:07 am

    #705008

    How about disabling the trigger first (alter table tblname disable trigger trname).

     

    Then dropping/modifying it.

  • May 8, 2007 at 11:55 am

    #705026

    Thanks for the suggestion, tried:

    ALTER

    TABLE Products DISABLE TRIGGER iu_Products_CartPrice;

    still locked/blocked everyone using the web because the Products is used to show our products on the web when customers come to it...

    We get several thousand hits per day. 

  • May 8, 2007 at 1:27 pm

    #705054

    There seriously can't be that many hits a second to continuously block that object.  Are you sure that there isn't a dead lock already in place???

  • May 8, 2007 at 2:11 pm

    #705065

    There are no deadlocks/locks/blocks until I attempt to alter/drop/disable that trigger, than my PID takes over and just simply locks and hangs everyone and everything wanting to touch the Products table.  The Products table is used for more than the web since it is the main table used by all applications web or internal which contains a current list of the company's 60,000 SKU's and their attributes.  This table is used by many processes and applications beside the web.

    I am left to wake up tonight and kick everyone off the database and make my change to this table before the backup runs, boy I can't wait to get to Oracle 10g.  I've just about had it with SQL Server.  MS-SQL Server is good for small to mid-sized activity but it's lock escalation is very seriously beginning to become a problem for us, noted by the inability to modify this simple trigger.  thanks for responding...

  • May 8, 2007 at 2:32 pm

    #705068

    I can't wait to see what other dbas have to say about this one.  I'm sure there must be a way for you to make this change without stopping the app from running.  I'm just not that good with locks in sql server to be of much help.

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

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