I created and successfully tested a trigger on a test database. Now that I
want to put this on a production system, the create trigger statement takes
way too long to complete. I cancelled after a few minutes. The test
trigger took just a second to create. The test and production databases are
identical in design. Only difference is that there are users in the
production system.

Any ideas?
Thanks

rdraider wrote:
> I created and successfully tested a trigger on a test database. Now that I
> want to put this on a production system, the create trigger statement takes
> way too long to complete. I cancelled after a few minutes. The test
> trigger took just a second to create. The test and production databases are
> identical in design. Only difference is that there are users in the
> production system.

When someone is accessing the target table you can't add anything to it
(indexes, triggers, constraints, etc.). Best to add the trigger during
off-hours.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

rdraider (rdraider@sbcglobal.net) writes:
> I created and successfully tested a trigger on a test database. Now
> that I want to put this on a production system, the create trigger
> statement takes way too long to complete. I cancelled after a few
> minutes. The test trigger took just a second to create. The test and
> production databases are identical in design. Only difference is that
> there are users in the production system.

Does the trigger by chance access a linked server?

Else, I guess it could be blocking from a running query. Use sp_who
to determine if you have any blocking. The Blk column is non-zero if
the spid on the row is blocked. Blk is the spid of the blocker.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx