Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question
    
What are the wait types for the blocked processes? Do you have the ability to initialize from a backup? –  Thomas Stringer Dec 12 '12 at 11:30
    
@Thomas Stringer Sorry Thomas, this is an existing publication that is working fine... I have edited my original post to make that clear –  DamagedGoods Dec 12 '12 at 11:35
    
What are the waits? What resource(s)? Is the snapshot process longer duration now? –  Thomas Stringer Dec 12 '12 at 14:14
    
I didn't have time to look into the wait types, etc as the entire website was frozen so I killed the snapshot agent to bring it back to life... previously this would take a few seconds to generate this 3 tables with no effect on the site. i'm going to have to run it at 1am at a low period to try and see what is going on –  DamagedGoods Dec 12 '12 at 17:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.