BUG: A Process May Stop Responding Several Seconds with Waittype 0x0020 (236783)
The information in this article applies to:
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.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbBug kbpending KB236783 |
|---|
|