Lock Timeout in Replication Create Publication

Share this Post

Normally, the Snapshot create publication script only needs to lock the table long enough to generate a Create Table script for the Distribution Agent to apply to the Subscriber.  However, the create Publication was being blocked on the Publisher.  We discovered a database trigger used to AUDIT all schema modification on the Publisher was root cause for the Create Publication hanging.

CREATE trigger [TRIG_DDL_CHANGES]
   on database
   for create_table, drop_table, alter_table, create_procedure,alter_procedure,drop_procedure,create_function,
            alter_function,drop_function
as
   insert AUDIT_DDL_CHANGES values (host_name(),eventdata())
GO
Temporary Solution

Disable the database triggers while creating the Publication.

DISABLE TRIGGER ALL IN DATABASE 
-- or -- 
DISABLE Trigger ALL ON ALL SERVER

Share this Post

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.