SQL Server Performance


Discussion in 'Performance Tuning for SQL Server Replication' started by lara99, Jan 5, 2008.

  1. lara99 New Member

    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.
  2. ndinakar Member

    You dont want the Distribution database to grow during your replication peak hours as it will pause all activity, expand and resume activity. This can build up a huge backlog of transactions during the growth. So you want to preallocate the DB as much as you can and constantly monitor the growth. If you are running out of space you want to expand it manually during mininum load.
    Secondly, you can move distribution DB to subcsriber if you have more space there but that will involve removing replication and re-setting it up.
    Do you have users modifying data at ths subscriber end? Or do you have Subscriptionstreams parameter set to anything other than the default of 0 in the distribution agent?

Share This Page