I have a fairly heavy update workload in my database, and I have transactional replication enabled. When I’m monitoring disk activity I’ve noticed that there are a lot of reads, as well as writes, on the transaction log. Why is that?
This is a question we get asked a lot—why is something reading from my transaction log? Many times I’ve seen it stated that the transaction log is “write-only” or “write-mostly,” but that isn’t true. There are many processes and features that can cause reads of the transaction log, and transactional replication is one of them.
Transactional replication works by having a SQL Server Agent job periodically read the publication database’s transaction log on the Publisher to find committed changes to tables in the various publications you’ve defined. The changes are put into the distribution database on the Distributor for subsequent pushing or pulling to the subscription database(s) on the Subscriber(s). This process generates some of the read activity you’re seeing on your transaction log. (You can get a 200-level overview of transactional replication and other high-availability features in my MSDN white paper "High Availability with SQL Server 2008."
The most common cause of read activity on the transaction log is a backup operation. Transaction log backups (obviously) read all the transaction log records generated since the last transaction log backup completed. Full and differential backups must also read some transaction log records to guarantee that the restored database is transactionally consistent.
One feature that you might not expect to cause transaction log reads is a database snapshot (one you create manually or the one that’s created automatically when you run a DBCC CHECK* command). When a database snapshot is created, crash recovery is run on the database that the snapshot is based on, but the recovery is run into the database snapshot, making it a transactionally consistent, point-in-time view of the source database.
There are quite a few other things that can cause transaction log reads. See my recent blog post “
What can cause log reads and other transaction log questions” for more information.
