FIX: DELETE Statement May Cause Server to Become UnresponsiveID: Q176999
|
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
version 6.5. This problem has been corrected in U.S. Service Pack 5a
for Microsoft SQL Server version 6.5. For information about
downloading and installing the latest SQL Server Service Pack, see
http://support.microsoft.com/support/sql/.
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.
Additional query words: create hang hung freeze frozen lock locked up
Keywords : kbusage SSrvGen kbbug6.50 kbfix6.50.SP5
Version : winnt:6.5
Platform : winnt
Issue type : kbbug Last Reviewed: April 21, 1999