We have a transaction replication initialized from backup on a highly transactional OLTP database. When we add new articles(either from SSMS or using T-SQL) it causes massive blocking as the adding of the article process is trying to acquire schema mod lock on all the articles in the publication and not just the one that is being added. Exactly the same problem listed here.

Does anyone know if this can be avoided?

T-SQL Script used for adding new article:

exec sp_addarticle 
   @publication = N'blocking', 
   @article = N'rep6', 
   @source_owner = N'dbo', 
   @source_object = N'repl6', 
   @type = N'logbased', 
   @identityrangemanagementoption = N'manual', 
   @destination_table = N'repl6', 
   @destination_owner = N'dbo'

EXEC sp_addsubscription
   @publication = 'blocking',
   @article = N'rep6',
   @subscriber = 'sql3\s14',
   @destination_db = 'repl2',
   @sync_type = N'replication support only'

Edit: The issue seems to be with sp_addsubscription. sp_addarticle just needs schema mode lock on the article that is being added. But sp_addsubscription needs schema mod lock on all the existing articles in the publication. This is also true when running sp_addarticle and then sp_refreshsubscriptions.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.