FIX: Dump File Handle Not Released After Error on Load Table

ID: Q166758

The information in this article applies to:
BUG #: 16774 (6.5)

SYMPTOMS

If SQL Server runs out of locks while loading a table, you will receive the following error:

error 1204: SQL Server has run out of LOCKS. Re-run your command when there are fewer active users, or ask your System Administrator to reconfigure SQL Server with more LOCKS.
You cannot delete the dump file containing the table dump, and subsequent loads result in the following error:
error 3201: Can't open dump device '%.*s', device error or device off line. Please consult the SQL Server error log for more details.
This occurs even after closing all connections to SQL Server.

You must stop and restart SQL Server before you can delete the dump file or load the table again. However, you will still be able to run other queries in SQL Server without having to stop and restart SQL Server.

See the SQL Server Books Online for the syntax of the LOAD TABLE command.


WORKAROUND

To work around this problem, increase the number of configured locks. For more information on how to do this, see the SQL Server Books Online.


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 getting the error 1204, sp_who no longer shows the spid loading the table, and sp_lock no longer shows the ex_extent locks that were being held during the load. After closing all connections to SQL Server, the file properties for the dump file even show that there are no users with this file open, and that there are zero locks on the file.

However, you cannot delete this physical dump file or load the table, due to the following error:

dbsreadcheck: Backup device 'C:\MSSQL\BACKUP\testdmp.DAT' failed to open, operating system error = 32
This error occurs because process cannot access the file; it is being used by another process.

Additional query words: period 


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

Last Reviewed: April 8, 1999