FIX: An INSERT SELECT into a Table with IRL May Cause Error 818 (198653)
The information in this article applies to:
This article was previously published under Q198653
BUG #: 17607 (SQLBUG_65)
SYMPTOMS
An INSERT .. SELECT statement into a table with the Insert Row-level Locking (IRL) option enabled may return the following error to the client:
Msg 818, Level 19, State 1
There is no room to hold the buffer resource lock BUF pointer = 0x11b1cc0, page ptr = 0x1646800, virtpage = 117440834, dbid = 4, status = 0x100c in SDES SDES pointer = 0x3f4d2b4
The SQL Server is terminating this process.
This error occurs only if the following conditions exist: - IRL has been enabled for the table.
-and-
- There is a substantial amount of data in the table before the INSERT .. SELECT statement, so that a page split occurs.
WORKAROUND
To work around this problem, remove IRL from the table.
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.
MORE INFORMATION
After this errors occurs, the query is stopped but the spid remains and it does not release the locks it acquired. If the INSERT .. SELECT statement was from a temporary table, the spid holds onto locks in several system tables in tempdb, causing other clients to be blocked. You cannot kill the spid, and the only way to remove it is to shut down and restart SQL Server.
If you do not create a nonclustered index on the table and you execute the INSERT .. SELECT statement, you will still get the error 818, but the spid will be terminated and no locks will be held.
| Modification Type: | Major | Last Reviewed: | 3/14/2006 |
|---|
| Keywords: | kbBug kbfix KB198653 |
|---|
|