BUG: DELETE Statement May Cause Server to Become Unresponsive

• Microsoft SQL Server version 6.5

SYMPTOMS

Executing a DELETE statement against a table that has an index defined using a FILLFACTOR of 100 percent may cause the connection or the entire server to stop responding.

If SQL Server has more than one processor available, the connection will stop responding, but work can continue on other connections. A KILL command will not terminate the SPID. The only way to clear the problem is to cycle SQL Server.

If SQL Server has only one processor to work with, the entire server will stop responding. To regain control of SQL Server, you need to cycle Windows NT.

CAUSE

The connection is caught in an infinite loop.

WORKAROUND

To work around this problem, allow some free space on your index pages by specifying a FILLFACTOR of less than 100 percent (such as using FILLFACTOR=80). In addition to avoiding exposure to the above problem, doing this allows SQL Server to process your DELETE statement more efficiently.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5. We are researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

The type of index involved makes no difference to this problem. That is, it can happen with clustered, non-clustered, unique, and non-unique indexes.

So far, this problem has only been observed using SQL Server built with the binary sort order (the code page makes no difference). The same tests on SQL Server built with a dictionary sort order did not result in exposure to this problem.