The following is one of 15 replication tips in 15 minutes. Click here to return to the full list of tips.
8 of 15 replication tips: Maintain your log
The Log Reader Agent reads your transaction log by looking for transactions that are marked for replication. When the Log Reader Agent starts, it scans the entire log looking for the last transaction it read and starts reading from there. The smaller your log is, the less work the Log Reader Agent has to do. This is especially important if your Log Reader Agent is scheduled (a very bad idea) or more often when your Log Reader Agent fails and is restarted.
To maintain your log size, I recommend running in the Full Recovery Model (replication works with all recovery models) and dumping your log frequently -- sometimes as frequently as every five minutes. As your log size increases, it creates multiple Virtual Log Files (there will be four VLFs by default). Many of them will degrade performance slightly but will have greater impact the performance of your log reader, especially on restart.
When you dump your transaction log, all VLFs with committed transactions are reset to a status of 0, which means they can be used again (you can view this by issuing a DBCC loginfo(1)). VLFs with open transactions or transactions marked for replication that have not been marked as replicated will have a status of 2, which means this VLF is open.
To minimize the size of your transaction log you will need to back up the log, and then issue a DBCC SHRINKFILE. You may have to do this several times to shrink your log file to a size where it won't autogrow between log dumps. Autogrow will cause performance degradation, so don't get carried away here.
If your log reader agent does fail, you may find that your log will grow significantly, and on restart the log reader agent will fail. To fix this, set ReadBatchSize to 50 and QueryTimeout to 300.
Click here to return to 15 replication tips in 15 minutes.
About the author: Hilary Cotter has been involved in IT for more than 20 years as a Web and database consultant. Microsoft first awarded Cotter the Microsoft SQL Server MVP award in 2001. Cotter received his bachelor of applied science degree in mechanical engineering from the University of Toronto and subsequently studied both economics at the University of Calgary and computer science at UC Berkeley. He is the author of a book on SQL Server transactional replication and is currently working on books on merge replication and Microsoft search technologies.
