The latency of a transactional replication is high in SQL Server 2005 when the value of the "Initial Size" property and the value of the Autogrowth property are small

Extended support for SQL Server 2005 ended on April 12, 2016

If you are still running SQL Server 2005, you will no longer receive security updates and technical support. We recommend upgrading to SQL Server 2014 and Azure SQL Database to achieve breakthrough performance, maintain security and compliance, and optimize your data platform infrastructure. Learn more about the options for upgrading from SQL Server 2005 to a supported version here.

SYMPTOMS
Consider the following scenario. In Microsoft SQL Server 2005, you create a transactional replication. For the transaction log file of the publication database, the value of the Initial Size property and the value of the Autogrowth property are small. In this scenario, the latency of the transactional replication is high.

For more information about how to measure the latency of a transactional replication, visit the following Microsoft Developer Network (MSDN) Web site: Additionally, when you synchronize a subscription with the publication, you receive the following error message under Status in the View Synchronization Status dialog box:
Performance critical.
CAUSE
This problem occurs because many virtual log files are generated. The log reader process must scan every virtual log file for transactions that are marked for replication. You can see this behavior by tracing the performance of the sp_replcmds stored procedure. The log reader process uses the sp_replcmds stored procedure to scan the virtual log files and to read the transactions that are marked for replication.

For more information about the performance effects of virtual log files, visit the following MSDN Web site:
WORKAROUND
To work around this problem, follow these steps:
  1. Add a transaction log file to the publication database. The transaction log file should meet the following requirements:
    • The initial size is large.
    • The value of the Autogrowth property is large.
    To add a transaction log file to the publication database, use the ALTER DATABASE statement together with the ADD LOG FILE clause. For more information about the ALTER DATABASE statement, visit the following MSDN Web site:
  2. Use the DBCC SHRINKFILE statement to reduce the size of the transaction log files that contain many virtual log files. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
    907511 How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005
STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
MORE INFORMATION
When you create a database that will be used as a publication database in a transactional replication, we recommend that you specify a large initial size and a large value of the Autogrowth property. The initial size should allow for peak periods. Peak periods include periods in which large batches prevent the log from clearing at the next transaction log backup. This behavior is caused by transactions that are still in the replication process. Set up alerts for when the log file approaches a full state. Additionally, perform file growth manually when you have to control size and timing. Autogrow should not be the primary method that you use to manage file sizes.
Properties

Article ID: 949523 - Last Review: 03/05/2008 00:32:20 - Revision: 1.2

Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition

  • kbexpertiseadvanced kbtshoot kbprb KB949523
Feedback