FIX: An INSERT SELECT into a Table with IRL May Cause Error 818

• Microsoft SQL Server version 6.5

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.

• 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 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.

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.

Additional query words: lock turn turned on rowlevel shutdown terminate wait type

Keywords          : SSrvTran_SQL kbbug6.50 kbfix6.50.SP5 
Version           : winnt:6.5
Platform          : winnt 
Issue type        : kbbug 

Last Reviewed: May 4, 1999