Select the product you need help with

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

Article ID: 949523 - View products that this article applies to.

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:

http://msdn2.microsoft.com/en-us/library/ms151846.aspx

(http://msdn2.microsoft.com/en-us/library/ms151846.aspx)

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.

Back to the top | Give Feedback

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:

http://msdn2.microsoft.com/en-us/library/ms179355.aspx

(http://msdn2.microsoft.com/en-us/library/ms179355.aspx)

Back to the top | Give Feedback

WORKAROUND

To work around this problem, follow these steps:

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:
http://msdn2.microsoft.com/en-us/library/ms174269.aspx
(http://msdn2.microsoft.com/en-us/library/ms174269.aspx)
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
(http://support.microsoft.com/kb/907511/ )
How to use the DBCC SHRINKFILE statement to shrink the transaction log file in SQL Server 2005

Back to the top | Give Feedback

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.

Back to the top | Give Feedback

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.

Back to the top | Give Feedback