Article ID: 177114
Article Last Modified on 3/14/2006
APPLIES TO
- Microsoft SQL Server 6.5 Service Pack 3
This article was previously published under Q177114
BUG #: 17399 (NT: 6.5)
SYMPTOMS
Deleting a table that includes at least one text or image datatype by using Transact-SQL statements EXISTS or NOT EXISTS and a correlated subquery, will not properly escalate page locks to a table lock. Although sp_lock will show an Ex_table lock on the table, Ex_page locks will continue to be acquired, in excess of the LE threshold, for all pages involved in the deletion. On larger tables, such deletions can exhaust available locks and generate a system message 1204:
This is a regression bug for 6.50.258 Service Pack 3 (SP3). The symptoms are not seen in 6.50.201 (base release) or 6.50.240 (SP2).
WORKAROUND
You can work around the problem by using either of these methods:
- Separate the correlated subquery from the deletion by using the correlated subquery to move data into a temporary table. or
- Use a cursor on a unique index to delete the rows one by one.
STATUS
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:
197177 INF: How to Obtain SQL Server 6.5 Service Pack 5a
For more information, contact your primary support provider.
Additional query words: prodsql sp3 sp5 correlated escalation escalate decouple
Keywords: kbbug kbfix KB177114
