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

• Microsoft SQL Server version 6.5

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, perform the following steps:

1. In SQL Enterprise Manager, right-click the server name click Configure on the shortcut menu.



2. On the Server Options tab, click Parameters.



3. Add a new parameter as -T3309.

STATUS

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

Additional query words: hang hangs hung 0x20

Keywords          : SSrvAdmin kbbug6.50 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: July 7, 1999