I am experiencing serious locking when adding new articles to an existing transactional replication publication, which is hanging the website. This is the first time I have attempted this since I recreated the replication without issues after my upgrade to SQL 2012.
This is a process I have completed when on sql 2008r2 dozens of times when the publisher database was much busier, so don't think this is traffic related. I can see from the replication monitor it only seems to be selecting those specific tables, its hard to tell what is causing the blocking with sp_who2 as the whole site gets jammed with hundreds of blocks so I just have to kill it as cant take the site down to troubleshoot.
I run this to make sure it only captures the new articles
EXEC sp_changepublication @publication = 'statics', @property = N'immediate_sync', @value = 'FALSE' GO
I am trying to add 3 lookup tables, only a few rows each and currently not in use by the system, so this leads me to believe that its locking all the articles in this publication which would indeed lock the website. am I missing some new setting in SQL 2012?
for reference I am adding new articles with the following code -
sp_addarticle @publication='statics', @article='SupplierCarrierLink_tbl', @source_object='SupplierCarrierLink_tbl' GO EXEC sp_addsubscription @publication = 'Statics', @article = 'SupplierCarrierLink_tbl', @subscriber = 'CHICAGO', @destination_db = 'MLT-Replicated', @reserved='Internal' GO
it worked fine in my test and pre-production systems but obviously they don't really have any serious load. The only other difference I can think of is that my production is still running in 2008 compatibility mode, could this make a difference? or can anyone suggest why this behaviour has now changed ?