| Author |
Topic |
|
influent
Constraint Violating Yak Guru
367 Posts |
 Posted - 2012-09-05 : 14:19:10
|
| I did an update to 2 million rows of one table this morning, and I only updated one column. The updates came in batches of 100,000 or 200,000. Ever since then my transactional replication is way behind. There are over 2.4 million undistributed commands according to Replication Monitor. It says the estimated time to apply is 12 minutes but that's obviously wrong since it's been running way behind for a couple hours now. Is there any way I can remove the updates I did this morning from MSrepl_commands? I tried putting the results of sp_browsereplcmds into a table but it ran for 45 minutes and then I canceled the query. I don't want to restart replication with a new snapshot because it takes a long time (leaving users with no data for a while) and causes a lot of deadlocks. |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-09-05 : 14:27:17
|
| We are able to speed up applying a large backlog by running the distrib cleanup job more frequently. You may want to temporarily change your history to a smaller timeframe too.How many subscribers do you have? If only one, you should also change the setting to specify that it doesn't hold on to the data according to the retention value and rather deletes it after it has been applied to the subscriber and after the distrib cleanup job runs. This setting is there for performance reasons when you have one subscriber and don't need to sync other subscribers. It helped us out tremendously. We have an extremely busy transactional replication setup.Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
 |
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-09-05 : 14:32:16
|
| Thanks. I'm not sure how to do the things you've suggested, could you give me a link or a sproc name? Do I go to the Distribution properties and alter Transaction Retention? |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-09-05 : 14:57:45
|
| The distribution cleanup job has been saying this for the past hour (it's running every 5 minutes): Removed 0 replicated transactions consisting of 0 statements in 0 seconds (0 rows/sec). [SQLSTATE 01000] (Message 21010). The step succeeded.Right before that it said:Removed 7370746 replicated transactions consisting of 8119977 statements in 126 seconds (122942 rows/sec). |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-09-05 : 15:19:00
|
| Transaction retention and history retention in distributor properties. We have both set to 24 hours, although even a shorter retention would be better but we can't get our DW team to agree. On a SevA case with Microsoft a while back when replication couldn't keep up, MS recommended we use 12 hours or less. Our problem was due to just how many rows we had in the distribution database.How many subscribers do you have?Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-09-05 : 15:35:53
|
| Only one. I tried deleting all the rows from MSrepl_commands that corresponded with my batch updates and that got the queue down to 160k commands, but the queue instantly started going up again and new rows from the table I updated have not been transferred for hours.Couldn't setting the retention settings cause the subscription to be expired when I don't want it to? |
|
|
|
influent
Constraint Violating Yak Guru
367 Posts |
Posted - 2012-09-05 : 15:38:05
|
| Occasionally it says "2 transactions with 2 commands delivered" or some similarly low number, so it must be doing something, but I guess that's from the Publisher to the Distributor and not from the Distributor to the Subscriber. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2012-09-05 : 15:44:35
|
Yes that's from the publisher to the distributor. All of the data that you changed is already in the distribution database.With one subscriber, Microsoft recommends using immediate_sync=false for the publication. At least they did on the support case that I opened.EXEC YourDbName..sp_changepublication @publication = 'YourPubName', @property = 'allow_anonymous', @value = 'false'EXEC YourDbName..sp_changepublication @publication = 'YourPubName', @property = 'immediate_sync', @value = 'false' But please do some research on this first. What is recommended for my system on a SevA case may not be what's recommended for your system.And run the distrib cleanup job often as mentioned earlier, especially during a backlog scenario like this. Tara KizerMicrosoft MVP for Windows Server System - SQL Serverhttp://weblogs.sqlteam.com/tarad/Subscribe to my blog |
|
|
|
|
transactional replication WAY behind