CREATE TRIGGER is taking more than 30 minutes on SQL Server 2005

Question

On our live/production database I'm trying to add a trigger to a table, but have been unsuccessful. I have tried a few times, but it has taken more than 30 minutes for the create trigger statement to complete and I've cancelled it.

The table is one that gets read/written to often by a couple different processes. I have disabled the scheduled jobs that update the table and attempted at times when there is less activity on the table, but I'm not able to stop everything that accesses the table.

I do not believe there is a problem with the create trigger statement itself. The create trigger statement was successful and quick in a test environment, and the trigger works correctly when rows are inserted/updated to the table. Although when I created the trigger on the test database there was no load on the table and it had considerably less rows, which is different than on the live/production database (100 vs. 13,000,000+).

Here is the create trigger statement that I'm trying to run

CREATE TRIGGER [OnItem_Updated] 
    ON  [Item]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;

    IF update(State)
    BEGIN
     /* do some stuff including for each row updated call a stored 
       procedure that increments a value in table based on the 
       UserId of the updated row */
    END
END

Can there be issues with creating a trigger on a table while rows are being updated or if it has many rows?

In SQLServer triggers are created enabled by default. Is it possible to create the trigger disabled by default?

Any other ideas?

Answer 1

That's odd. An AFTER UPDATE trigger shouldn't need to check existing rows in the table. I suppose it's possible that you aren't able to obtain a lock on the table to add the trigger.

You might try creating a trigger that basically does nothing. If you can't create that, then it's a locking issue. If you can, then you could disable that trigger, add your intended code to the body, and enable it. (I do not believe you can disable a trigger during creation.)

Answer 2

I believe the CREATE Trigger will attempt to put a lock on the entire table.

If you have a lots of activity on that table it might have to wait a long time and you could be creating a deadlock.

For any schema changes you should really get everyone of the database.

That said it is tempting to put in "small" changes with active connections. You should take a look at the locks / connections to see where the lock contention is.

Answer 3

The problem may not be in the table itself, but in the system tables that have to be updated in order to create the trigger. If you're doing any other kind of DDL as part of your normal processes they could be holding it up.

Use sp_who to find out where the block is coming from then investigate from there.

Answer 4

Part of the problem may also be the trigger itself. Could your trigger accidentally be updating all rows of the table? There is a big differnce between 100 rows in a test database and 13,000,000. It is a very bad idea to develop code against such a small set when you have such a large dataset as you can have no way to predict performance. SQL that works fine for 100 records can completely lock up a system with millions for hours. You really want to know that in dev, not when you promote to prod.

Calling a stored proc in a trigger is usually a very bad choice. It also means that you have to loop through records which is an even worse choice in a trigger. Triggers must alawys account for multiple record inserts/updates or deletes. If someone inserts 100,000 rows (not unlikely if you have 13,000,000 records), then looping through a record based stored proc could take hours, lock the entire table and cause all users to want to hunt down the developer and kill (or at least maim) him because they cannot get their work done.

I would not even consider putting this trigger on prod until you test against a record set simliar in size to prod.

My friend Dennis wrote this article that illustrates why testing a small volumn of information when you have a large volumn of information can create difficulties on prd that you didn't notice on dev: http://blogs.lessthandot.com/index.php/DataMgmt/?blog=3&title=your-testbed-has-to-have-the-same-volume&disp=single&more=1&c=1&tb=1&pb=1#c1210