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

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:

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:

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

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. Therefore, you can avoid the log file growth.