Microsoft KB Archive/166758

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

Article ID: 166758

Article Last Modified on 3/14/2006

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition

-



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.

Additional query words: period

Keywords: kbbug kbfix kbusage KB166758

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.