FIX: Writelog Timeout When Replicating Cross-Database Transactions

ID: Q181557

The information in this article applies to:
BUG #: 17830 (NT: 6.5)

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 version 6.5. This problem has been corrected in U.S. Service Pack 5a for Microsoft SQL Server version 6.5. For information about downloading and installing the latest SQL Server Service Pack, see http://support.microsoft.com/support/sql/.

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:

Q167711 INF: Understanding Bufwait and Writelog Timeout Messages

Additional query words: prodsqlslow timeouts 2pc performance 


Keywords          : SSrvAdmin SSrvErr_Log SSrvProg SSrvRep kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug

Last Reviewed: April 21, 1999