FIX: DELETE Results in 8908 Error Message When You Use a Parallel Plan (275593)

SYMPTOMS

A DELETE statement with a subquery fails with this error message

Server: Msg 8908, Level 22, State 6, Line 4
Table Corrupt: Database ID 10, object ID 165575628, index ID 0. Chain linkage mismatch. (1:19776)->next = (1:19765), but (1:19765)->prev = (1:20614).

If all these conditions are true:

The query uses a parallel plan.
The query uses a MERGE JOIN during execution.
Tables involved in the DELETE query have an INDEX and the deleted table in particular have a CLUSTERED INDEX.

However, subsequent execution of DBCC CHECKDB shows no corruption, which confirms that this error message should not occur.

WORKAROUND

To work around this behavior, use one of these methods:

Disable parallel plan generation server-wide through the sp_configure system stored procedure or as a query hint for the problem query by using the "OPTION (MAXDOP 1)" clause.

NOTE: To disable parallel plan generation through the sp_configure system stored procedure, you must enable the Show advanced options configuration setting:
```
   sp_configure 'show advanced options', 1
   reconfigure with override
   go
   sp_configure 'max degree of parallelism', 1
   reconfigure with override
	   go
					
```
-or-
Force a different type of JOIN like a HASH or a LOOP as a query hint for the problem query. -or-
Create a non-clustered index instead of a clustered index or drop the clustered index on the deleted table.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 7.0. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server 7.0. For more information, click the following article number to view the article in the Microsoft Knowledge Base:

274799 INF: How to Obtain Service Pack 3 for Microsoft SQL Server 7.0 and Microsoft Data Engine (MSDE) 1.0

For more information, contact your primary support provider.