Lock Timeout in Replication Create Publication


Lock Timeout in Replication Create Publication

Chris Skorlinski
Microsoft SQL Server Escalation Services

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
Comments (0)