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


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q166758
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 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 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.
Modification Type:MajorLast Reviewed:3/14/2006
Keywords:kbBug kbfix kbusage KB166758
©2004 Microsoft Corporation. All rights reserved.