SQL Server Performance

Distribution Agent hogs resources on catch up

Discussion in 'Performance Tuning for DBAs' started by nicolai, Nov 12, 2002.

  1. nicolai New Member

    Hi. I hope this is the correct forum for this question.
    My company is in the SMS business, and we are running two SQL servers.
    Server 1: Pproduction, where we get about 50.000 entries in our log messages tables every day,
    Server 2: Reporting server. Used by our intranet website to produce traffic statistics, and other reports.

    The servers are on separate LANS.
    I have set up continous transactional replication between the two servers. Server 1 is the publiser, server 2 is the distributor and subscriber.

    The problem is when we for some reason has to turn off the log reader/distribution agent for a period. The log reader agent on server 1 quickly catches up with the system, while the distribution agent takes a very long time to catch up with the transactions.
    This is probably caused by a trigger on the tables where the transactions are inserted, and is not really a problem in itself. The problem is that the distribution agent hogfs all the resources on the
    server. Yesterday we had a situation where we stopped the distribution agent for about 40 mins. when we restarted it, the CPU usage on the server immidiately went up to 100 % and stayed there. This blocked everything else we tried to do on that server.

    Finally my question: Is there a way of telling the distribution agent to use less resources, I don't mind if this means it will take longer to catch up.
  2. royv New Member

    This is to be known with replication, that the resources involved are very hogish in nature. I would suggest considering log shipping. There is an excellent article on this site, just click on the advanced search link and type in log shipping. With SQL 2000, log shipping is built in and much less of a resource hog.


    "How do you expect to beat me when I am forever?"
  3. bradmcgehee New Member

  4. satya Moderator

    I second Royv's suggestion to look into Log shipping which is suitable for your kind of environment.

    HTH

    Satya SKJ

Share This Page