January 5, 2008 at 5:10 pm
hi all we have a situation here , we do lot of transactional replication, our distribution database is on our
main production server , every other day replication agent failure occurs with different reasons. Some of the
errors I observed are
Some of the errors I observed are
1.unable to open sp_replMerge......
2.while logreader agent running it is creating millions of locks(which i came over by changing the logreader agent profile)
3.subscription is failing with the error. row not found at the destination.
4.Server which is hosting distribution database is running out of space.
My question is do we have to put the distribution database to fixed size or autogrow in database option
and do we have to set the option autoshrink in the distribuiton database.
Any help on this would be helpful.
Thanks in advance.
January 6, 2008 at 10:28 am
The distribution database needs to be large enough to hold your changes, so size it appropriately, which may mean making it larger. You can set autogrow, but you don't want it growing on a regular basis. That's for emergencies.
NEVER use autoshrink. If your database needs to be that large, let it be that large. If it's a one time thing, manually shrink the files down.
January 6, 2008 at 11:54 am
Do you have the option to move use another server as your distributor?
I'm thinking that if your production server fills due to growth in your distribution database then this will cause you a lot more pain than if your distribution database filled on a separate box.
If you have your distribution database set to autogrow I would set it to grow by a fixed amount rather than a percentage amount.
All the live production databases I work with are set to have around 40% free space so we should never encounter an unplanned auto-grow.
I have a seen situations where push replication overwelmed the distributor. Our replication DBA fixed this by switching some of the subscriptions to PULL subscriptions. It adds some steps if you want to change the publication at a later date but it lessens the strain on the distributor.
January 24, 2008 at 1:13 am
Hi,
I'm not sure your distribution issues are responsible for all your errors...
Number 3 on your list looks suspect...looks like the data may be changing on the subscriber...is this right?
Graeme
January 25, 2008 at 5:04 am
Some things that have helped me reduce the pain of replication over the years:
As suggested earlier by David, move the distributor to a separate box if possible. Granted there may be times when the server is not doing much, but it is imperative that the distributor has the resources (cpu, memory, disk space, etc) necessary for the peak times of the day.
If at all possible, only allow read access to the subscriber database. If there is a way, sooner or later someone is going to update that database by accident, and that will result in the "row not found" error.
Depending on how much you are replicating, splitting into multiple publications definitely helps by reducing the time needed to reinitialize, update, etc.
Joe
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply