none
Commands in Distributor Not Replicating to Subscriber Database RRS feed

  • Question


  • Due to an increase in network utilization and more data, replication was backed up   The [MSrepl_commands] table in the distribution database has about 39 million rows to apply to the subscriber table.  The distributor agent keeps on timing out along with the following message "The process is running and is waiting for a response from the server". Is there any way to send smaller batches over to the subscriber?  I believe timeout is set to 30 minutes.  All 39 million rows does need to be applied.  Any help will be appreciated, thank you.

    Ted


    tae yang


    • Edited by taeyang Wednesday, December 3, 2014 10:26 PM remove unneeded sentence
    Wednesday, December 3, 2014 10:25 PM

Answers

  • Hi tae,

    You can tweak the time out setting to give the transaction some more time so it can process properly. To do this, please perform the following steps.

       1.Right click the Replication folder

       2.Click Distributor Properties and select General

       3.Click ‘Profile Defaults’

       4.Choose ‘Distribution Agents’ on left

       5.Click ‘New’ to create a new default agent profile

       6.Choose ‘Default Agent Profile’ from the list displayed, (to copy this)

       7.Pick a name for your new profile and update the QueryTimeout value in right column

       8.Save

       9.Choose to use this profile across all your replication sets. It is recommended to only apply to the agent that requires this change

       10.To individually assign the profile, open Replication Monitor and then in the left pane click your replication set

       11.In the right pane, select your desired agent, right click and change the profile to the new one you just created

    Reference:
    http://www.pythian.com/blog/sql-server-replication-quick-tips/
    http://replicationronnie.blogspot.com/2010/02/p2p-transactional-query-timeout-expired.html

    Thanks,
    Lydia Zhang


    If you have any feedback on our support, please click here.
    Thursday, December 4, 2014 8:56 AM
    Moderator

All replies

  • Hi tae,

    You can tweak the time out setting to give the transaction some more time so it can process properly. To do this, please perform the following steps.

       1.Right click the Replication folder

       2.Click Distributor Properties and select General

       3.Click ‘Profile Defaults’

       4.Choose ‘Distribution Agents’ on left

       5.Click ‘New’ to create a new default agent profile

       6.Choose ‘Default Agent Profile’ from the list displayed, (to copy this)

       7.Pick a name for your new profile and update the QueryTimeout value in right column

       8.Save

       9.Choose to use this profile across all your replication sets. It is recommended to only apply to the agent that requires this change

       10.To individually assign the profile, open Replication Monitor and then in the left pane click your replication set

       11.In the right pane, select your desired agent, right click and change the profile to the new one you just created

    Reference:
    http://www.pythian.com/blog/sql-server-replication-quick-tips/
    http://replicationronnie.blogspot.com/2010/02/p2p-transactional-query-timeout-expired.html

    Thanks,
    Lydia Zhang


    If you have any feedback on our support, please click here.
    Thursday, December 4, 2014 8:56 AM
    Moderator
  • Hi Lyndia,

    Thank you for your response.  I was able to get the Distributor running and replicating transactions by creating a new agent profile and increasing the QueryTimeout.  I used the following link, which does the same thing as you indicated in your response.  Again thank you for your quick response.  

    http://www.sql-server-performance.com/2013/replicating-a-volume-of-large-data-over-the-via-transactional-replication/


    tae yang

    Thursday, December 4, 2014 9:32 PM