Microsoft KB Archive/181557

= FIX: Writelog Timeout When Replicating Cross-Database Transactions =

Article ID: 181557

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q181557



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 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

Additional query words: prodsqlslow timeouts 2pc performance

Keywords: kbbug kbfix kbsqlserv650sp5fix KB181557

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.