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
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:
- Add a transaction log file to the publication database. The transaction log file should meet the following requirements:
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:
- The initial size is large.
- The value of the Autogrowth property is large.
- 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:
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. 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.