FIX: Writelog Timeout When Replicating Cross-Database Transactions (181557)

SYMPTOMS

If a published table is modified as part of a two-phase commit transaction, SQL Server may become sluggish or begin to log writelog timeout messages to the SQL Server errorlog. The writelog timeout message will look like:

writelog: timeout, dbid 7, dbstat2 0x100002(0x100002), q1/q2 0/0,
owner=17, waittype=0

The problem is more likely to occur on a computer with a single or dual processors, where the default 'SMP Concurrency' option would throttle SQL Server to using only a single processor. Computers with more processors may still exhibit short bottlenecks, but will be much less likely to see the writelog timeout messages.

Internally, SQL Server uses two-phase commit protocol when performing any cross-database transactions. Two-phase commit may also be done programmatically through DB-Library applications, or applications using Microsoft Transaction Server (MTS).

CAUSE

When the logreader task runs sp_repldone and finds a pending two-phase commit transaction, it waits for a short period of time to allow the transaction to complete. During this time, it holds an internal resource used for synchronization, thus preventing other processes from running. Processes attempting to write log records may therefore see timeout messages.

WORKAROUND

To work around this problem, change the logreader to a Recurring task, rather than Auto Start. Even if the task is run every minute, the frequency with which sp_repldone is run will decrease, thereby minimizing the chance of stalling on a transaction in a prepared state.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:

197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a

For more information, contact your primary support provider.

MORE INFORMATION

Writelog timeout messages occur when SQL Server waits for longer than the configured 'resource timeout' period for a log write operation to complete. These timeouts typically indicate an I/O bottleneck. If this particular scenario is being encountered, the disk subsystem will essentially be idle. For detailed information about troubleshooting scenarios where the disk subsystem is busy during these timeout errors, please see the following article in the Microsoft Knowledge Base:

167711 INF: Understanding Bufwait and Writelog Timeout Messages