Microsoft KB Archive/236783

= BUG: A Process May Stop Responding Several Seconds with Waittype 0x0020 =

Article ID: 236783

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



This article was previously published under Q236783



BUG #: 18738 (SQLBUG_65)



SYMPTOMS
Under a high transaction load, you may observe a process staying in a wait state for up to one minute and possibly blocking other processes. The sp_who2 stored procedure will show the process as having a status of RUNNABLE and being in Command COMMIT TRANSACTION. Additionally, when querying the sysprocesses table, this process will have a waittype of 0x0020.



CAUSE
When committing a multi-database transaction, the ENDXACT log record for the transaction must be changed from a PREP status to a COMMIT status. If the page containing this log record is currently flushed to disk while a transaction wants to perform this status update, this process will have to wait until the resource timeout elapses (starting with SQL Server 6.5 Service Pack 5, this timeout is at least 60 seconds, even if the "resource timeout" configuration setting has a lower value).



WORKAROUND
To work around this problem, run SQL Server with trace flag 3309 enabled. This changes the way the COMMIT logging is performed for multi-database transactions and thus avoids the additional update of the ENDXACT record. To add trace flag 3309 as a SQL Server startup parameter:



STATUS
Microsoft has confirmed this to be a problem in SQL Server 6.5.

Additional query words: hang hangs hung 0x20

Keywords: kbbug kbpending KB236783

-

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

© Microsoft Corporation. All rights reserved.