SQL Server Performance

Transactional repl large tables issue -sqlsvr2000

Discussion in 'Performance Tuning for SQL Server Replication' started by ttessitore, May 11, 2006.

  1. ttessitore New Member

    I am attempting to set up one way transactional replication on SQL Server 2000 in order to create a reports database. I have ninety tables to copy. The database is large and I cannot transfer it all in one publication. I have set up multiple publications. Replication on the smaller tables work fine. I have a problem when I try to setup publications for large tables.

    I have a publication that has two tables. On table has 8 million rows and other has 20 million rows. I set up the publication using the wizard and everything appears fine. The sanpshot runs successfully. The problem happens during the bulk copy phase. I watch the agent as it copies 100000 rows at a time. Based on the messages from the agent, it appears to finish , but the tables remain locked and the transaction log increases in size. It does this for about an hour and then transaction log reduces in size and it tries to do the bulk copies again. It never finishes. The transaction log was only 40% used and their is plenty of space in the repldata directory.

    Does anybody have an idea on what the problem could be?

    Thanks,
    Tom
  2. Twan New Member

    Hi ya,

    try opening the replication job that distributes the data and increase the timeout parameter

    Cheers
    Twan

Share This Page