FIX: DELETE Statement May Cause Server to Become Unresponsive

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.

The connection is caught in an infinite loop.

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.

Microsoft has confirmed this to be a problem in SQL Server 6.5. This problem has been corrected in Service Pack 5a for Microsoft SQL Server 6.5.For more information, click the following article number to view the article in the Microsoft Knowledge Base:For more information, contact your primary support provider.

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.