none
Delivering replicated transactions - Replication seems stuck RRS feed

  • Question

  • Hi,

    We are trying to set up replication for a very big partitioned table on SQL Server 2012. It was not possible to initialize the replication from backup so had to follow snapshot option.  Concurrent snapshot was generated and same was delivered on subscriber but after that replication monitor shows 'Delivering Replicated Transactions' from last three days. 

    Checked further and found concerned SPID was executing beiow stored procedure, please note appcrm is the schema and callDetails_new is the table on subscriber. On further investigation, SPID was executing multiple threads and prominent wait was CXPACKET Wait and I could see logical read count was increasing continuously. 

    [dbo].[sp_MSins_appcrmCallDetail_New_msrepl_ccs]

    Looked at execution plan for SP and found estimated number of row to be "1" and missing index details - it was non-clustered index that I had disabled so to fasten non-SELECT DML operations. This is first time we are trying to set up replication for such big table and our hands are tied to go with our options - please let us know if you got a clue on this. As expected, post bulk loading the table , Primary key was created - will it help not to have PK on subscriber during set up ? Shall we update the stats for concerned table on subscriber ? Are their any specific guidelines for replication set up for big tables?

    Appreciate your help. Thank you ! 

    Tuesday, September 20, 2016 5:40 AM

Answers

All replies

  • This is Transactional Replication right?

    You do understand that replication runs continuously so that any changes on the publisher get detected and then sent out to the subscriber?   It should keep on delivering replicated transactions.

    You want to have a look at this article https://msdn.microsoft.com/en-au/library/ms151178.aspx and use the techniques there to see if your replication is up to date and properly delivering transactions to the subscriber.


    Martin Cairney SQL Server MVP

    Wednesday, September 21, 2016 1:41 AM
  • This is Transactional Replication right?

    You do understand that replication runs continuously so that any changes on the publisher get detected and then sent out to the subscriber?   It should keep on delivering replicated transactions.

    You want to have a look at this article https://msdn.microsoft.com/en-au/library/ms151178.aspx and use the techniques there to see if your replication is up to date and properly delivering transactions to the subscriber.


    Martin Cairney SQL Server MVP

    Thanks Martin for response !

    Yes, it is Transnational Replication. Stored procedure was taking too long as there was no supporting index and it was doing clustered index scan for such a large table. In those days, row count didn't increase on subscriber.

    reads writes logical_reads: 

    1084087376 78 1115040387 

    I have created that missing index and re-initialized the replication. Will post the progress on this thread.

    Thank you.

    Wednesday, September 21, 2016 10:20 AM
  • you should have investigated to see what was been sent and why it was struck. It could have been blocked by a user process on the other side. It could have been the post snapshot synchronization where it was catching up.

    It could have been a large batch operation which was being replicated.

    The particular stored procedure is only fired after the snapshot is applied and when you are doing the concurrent snapshot option.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Wednesday, September 21, 2016 1:40 PM
    Moderator
  • you should have investigated to see what was been sent and why it was struck. It could have been blocked by a user process on the other side. It could have been the post snapshot synchronization where it was catching up.

    It could have been a large batch operation which was being replicated.

    The particular stored procedure is only fired after the snapshot is applied and when you are doing the concurrent snapshot option.


    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Thank you Hilary for response !

    Although there was no blocking and distribution clean up job was disabled to avoid blocking for time being, said SP was taking just too long (had waited for three days) to complete after initial snapshot delivery. It was concurrent snapshot and due to different set of indexes on publisher and subscriber, stored procedure was doing index scan as shown in the execution plan and read/write count difference.

    After creating required index on subscriber and re-initializing the replication, concurrent snapshot got generated and delivered as well as said SP completed it's execution in 12+ hours. I could see commands are flowing continuously and data is in sync.

    Replication looks good now.

    Hope this helps someone !

     

    Friday, September 23, 2016 7:48 AM
  • Anil,

    It appears that your distribution agent is stuck. Are you using Default Agent Profile? If yes then You may want to use custom Agent Profile and adjust value for CommitBatchsize, CommitBatchThreshold, and PolingInterval.


    Thanks, Mohan Kumar - Please mark the post as answered if it answers your question.

    Friday, September 23, 2016 4:54 PM
  • Hello Mohan,

    Thank you for your response !

    I just checked distribution agent is running with default values for parameters - Do you recommend some range of values to set ? Will do testing to find out what values works best for us.

    Appreciate your help !

    Monday, September 26, 2016 9:30 AM
  • Your problem is with the catch up phase of the concurrent snapshot option. You might find it beneficial to change commitbatchsize and commitbatchthreshold to 10000 to see if that helps.

    looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941

    Monday, September 26, 2016 2:54 PM
    Moderator
  • Thank you Hilary for response !

    Will try adjusting the value for those parameters ! 

    Wednesday, September 28, 2016 5:34 AM