SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Replication (2008)
 transactional replication WAY behind
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/05/2012 :  14:19:10  Show Profile  Reply with Quote
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

USA
36952 Posts

Posted - 09/05/2012 :  14:27:17  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/05/2012 :  14:32:16  Show Profile  Reply with Quote
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?

Edited by - influent on 09/05/2012 14:36:46
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/05/2012 :  14:57:45  Show Profile  Reply with Quote
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).
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36952 Posts

Posted - 09/05/2012 :  15:19:00  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/05/2012 :  15:35:53  Show Profile  Reply with Quote
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?
Go to Top of Page

influent
Constraint Violating Yak Guru

USA
367 Posts

Posted - 09/05/2012 :  15:38:05  Show Profile  Reply with Quote
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.

Edited by - influent on 09/05/2012 15:38:44
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36952 Posts

Posted - 09/05/2012 :  15:44:35  Show Profile  Visit tkizer's Homepage  Reply with Quote
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 Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000