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'
The issue seems to be with
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