SQL Server Performance

Replicatoin -- suggestion please

Discussion in 'Performance Tuning for SQL Server Replication' started by shalini.dba, Jan 13, 2008.

  1. shalini.dba New Member

    hi all I need kind of suggestion and it is very important , we usually do lot of replication.
    to describe roughly we have 10 publisher and upto 20 t0 25 articles which are loaded with many tables.
    some of the publication generates from serverA which is configured as its own distributor.
    and almost half of the publication is coming from other server say ServerB but all of the publication has a common
    distributor database. Every other day we have problems like over replication agents go down. with different error messages
    some of the errors we get are.
    1.unable to execute sp_replcommands the transaction log of the publishing database is full.
    2.The process could not execute sp_replcmds27hp
    3.some times the server hosting the distribution database go out of space OUR DISTRIBUTION DATABASE IS SET TO AUTOGROW.
    4.sometimes the logreader agent creates millions of locks which in results causes the whole replication to fail.
    5.and most painful error is the distribution clean up agent blocks other agent and the replication will just go down.
    .We got different plans for this.
    Plan A:- we want to make a dedicated distributor server with giving enough space to the distribution database.
    plan B:- I prefer to make the second server ServerB its own distributor from where majority of the publicaitons are coming

    we had roller coaster ride using different agent profiles to make the things work for the time being. but is there a
    good way to fix this once for all. or atleast fix it in a way that error doestn't occur most frequently.
    I would be highly thnakful if you guys through some light on this and give some suggestions.
    Thank You.
    We rebuilt the ServerB from where half of the publicatoin are coming and we resotred only one system database, msdb
    to get back the dts packages and jobs. we didnt resotre master and model which is recommended. Would this have any relation
    with replicatoin failures.
    I am just curious.
    I would be thankful if you guys through some ideas.
    Thanks.

  2. ndinakar Member

    Looks like most of your problems arise from space issues - disk running out of space or logs becoming full..perhaps you need better monitoring in those areas - creating jobs/maintenance plans to track these spaces?
  3. satya Moderator

    To addup Dinakar's refer to http://www.sql-server-performance.com/tips/transactional_replication_p1.aspx for transactional and http://www.sql-server-performance.com/tips/replication_tuning_p1.aspx on generic replication.
    When adding a new transactional publication, SQL Server offers you many options to set for the publication. One of these options is the sync_method. This option lets you specify how the snapshot process works and also the locking behavior for the tables that are being replicated.
  4. shalini.dba New Member

    Thank You guys.

Share This Page