BUG: Writelog Timeout When Replicating Cross-DB Transactions

 Last reviewed: March 13, 1998
Article ID: Q181557
The information in this article applies to:
  • Microsoft SQL Server, version 6.5
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. A supported fix is now available, but has not been fully regression-tested and should be applied only to systems experiencing this specific problem. Unless you are severely impacted by this specific problem, Microsoft recommends that you wait for the next Service Pack that contains this fix. Contact Microsoft Technical Support for more information.

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, see the following article in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q167711
   TITLE     : INF: Understanding Bufwait and Writelog Timeout Messages

Additional query words: slow timeouts 2pc performance
Keywords : SSrvAdmin SSrvErr_Log SSrvProg SSrvRep
Version : WINNT:6.5
Platform : winnt
Issue type : kbbug
Solution Type : kbfix

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: March 13, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.