May 11, 2017 at 11:13 am
I have a problem. I have a server (ServerA SQL 2012) that is a publisher with 15 publications (transactional replication). Some of the published tables are very large (500,000,000 rows or more).
The distribution database is on ServerB and is over 250 GB due to a couple of issues that I have mainly fixed but the database is so large and slow that it is just killing replication. I have tried and tired to shrink it but replication is locking it to the point I can't. It has a pretty IO intense load due to the volume of replication.
I have set up a second distributor (ServerC) and would like to have half the publications use ServerB and half user ServerC for distribution, but it looks like this may not be possible? Do all the publications on a given server have to use the same distributor? Is there any way around this?
Any help would be greatly appreciated. Either with using two distributors or in getting the distribution database size under control. Thank you!!!
Jim
May 11, 2017 at 12:04 pm
Jim Youmans-439383 - Thursday, May 11, 2017 11:13 AMI have a problem. I have a server (ServerA SQL 2012) that is a publisher with 15 publications (transactional replication). Some of the published tables are very large (500,000,000 rows or more).The distribution database is on ServerB and is over 250 GB due to a couple of issues that I have mainly fixed but the database is so large and slow that it is just killing replication. I have tried and tired to shrink it but replication is locking it to the point I can't. It has a pretty IO intense load due to the volume of replication.
I have set up a second distributor (ServerC) and would like to have half the publications use ServerB and half user ServerC for distribution, but it looks like this may not be possible? Do all the publications on a given server have to use the same distributor? Is there any way around this?
Any help would be greatly appreciated. Either with using two distributors or in getting the distribution database size under control. Thank you!!!
Jim
A larger distributor can be related to so many things so it's hard to say in your case. A few of the things to check if you haven't:
Subscriptions not expiring
Cleanup jobs not running or not running often enough
Retention periods: history and transaction retention
Number and size of publications, articles
Large MSRepl_Commands table that may need manual cleanup
I have done multiple distributors on a server but not multiple servers. If it's the size of the database then just try multiple distributor databases - it's not that unusual to split up publications this way. And there is very good article on configuring and setting this up - check this link:
Scaling Out the Distribution Database
Sue
May 11, 2017 at 12:11 pm
Jim Youmans-439383 - Thursday, May 11, 2017 11:13 AMI have a problem. I have a server (ServerA SQL 2012) that is a publisher with 15 publications (transactional replication). Some of the published tables are very large (500,000,000 rows or more).The distribution database is on ServerB and is over 250 GB due to a couple of issues that I have mainly fixed but the database is so large and slow that it is just killing replication. I have tried and tired to shrink it but replication is locking it to the point I can't. It has a pretty IO intense load due to the volume of replication.
I have set up a second distributor (ServerC) and would like to have half the publications use ServerB and half user ServerC for distribution, but it looks like this may not be possible? Do all the publications on a given server have to use the same distributor? Is there any way around this?
Any help would be greatly appreciated. Either with using two distributors or in getting the distribution database size under control. Thank you!!!
Jim
And I forgot to ask - do you have a lot of free space available in the database since addressing the issues?
If that's the case and you need to do a one time shrinking of the database, just disable the agent jobs when trying the shrink.
Disable the clean up jobs and log reader agent job.
Sue
May 11, 2017 at 12:16 pm
You pretty much hit the nail on the head with this list. These were all issues.
Subscriptions not expiring
Cleanup jobs not running or not running often enough
Retention periods: history and transaction retention
Number and size of publications, articles
Large MSRepl_Commands table that may need manual cleanup
I think I am going to have to do as you suggest and stop replication and clean and shrink and reindex the db. Adding another Distrubtion database or two might be a good fix once I get the size issue under control.
Thank you!
May 12, 2017 at 1:39 am
You wont be able to split publications on ONE publisher to multiple distribution dbs.
One publisher can only have one distributor and one distribution db. ALL transactions from SERVERA will go via one distribution db. you can split different publishers to different distributions dbs on the same distributor. As far I understand all your publication reside on ServerA and this is only publisher u have in your setup?
Even the article provided says:
"Fortunately we can set up additional distribution databases on our distributor and assign a separate distribution database to each publisher." (publisher, not publication)
One more question: What is your retention period on that distribution db? how much data you keeping in? what is min(entry_time) in msrepl_transactions?
Im asking this as you may have problem as you keeping to much data in distribution db or cleanup (even if it is running often) not coping with deleting transactions.
Last thing. do you have same tables published across few publications? Lets say TableA is published in 3 publications. That kind of setup will generate more traffic in your distribution db. TableA will generate 3 rows in msrepl_commands for each transaction on that table.
BartL
Replication Blog
May 12, 2017 at 7:35 am
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply