Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
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.
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.aspxAdditionally, when you synchronize a subscription with the publication, you receive the following error message under Status in the View Synchronization Status dialog box:
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:
To work around this problem, follow these steps:
Microsoft has confirmed that this is a problem in the Microsoft products that are listed in the "Applies to" section.
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.
Article ID: 949523 - Last Review: March 5, 2008 - Revision: 1.2