Microsoft KB Archive/257852

{|
 * width="100%"|

INF: How to Recover from a "File in Use" Situation on SQL Server Startup

 * }

Q257852

-

The information in this article applies to:


 * Microsoft SQL Server version 7.0

-

SUMMARY
At startup, SQL Server attempts to obtain an exclusive lock on the database files. If a database file is being used by another process (for example backup software) at this time, the database cannot be recovered and is therefore not available for use. To resolve this problem, it is usually sufficient to make the database file available again and reinitiate the recovery process.

In SQL Server 7.0 it is no longer necessary to restart the SQL Server service and hence also affect users of different databases on the same server. Instead, you can run the recovery process for a specific database by using the DBCC DBRECOVER(dbname) command.

MORE INFORMATION
On SQL Server startup, a user database cannot be recovered if any of the database files for the database are in exclusive use by another application (non-exclusive use of database files does not necessarily prevent database recovery in SQL Server 7.0). A possible cause can be that backup software is holding on to the database file. You may see the following sort of messages in the SQL Server error log:

udopen: Operating system error 32 (The process cannot access the file because it is being used by another process.) during the creation/opening of physical device d:\mssql7\DATA\pubs.mdf.

FCB::Open failed: Could not open device d:\mssql7\DATA\pubs.mdf for virtual device number (VDN) 1.

Device activation error. The physical file name 'd:\mssql7\DATA\pubs.mdf' may be incorrect.

For example, performing the following steps demonstrates the problem:


 * 1) Stop SQL Server.
 * 2) Open the pubs database file in MSSQL\DATA\pubs_data.mdf with an application that requires exclusive access (such as Microsoft Word 2000).
 * 3) Start SQL Server.

The error messages described previously are reported to the SQL Server error log. The database may be shown as 'suspect' in SQL Server Enterprise Manager. Running the following query in the master database:

USE pubs The following message returns:

Server: Msg 945, Level 14, State 2, Line 1

Database 'pubs' cannot be opened because some of the files could not be activated.

You can use the following steps to resolve the problem:


 * 1) Run the following statement to confirm that the database property IsShutDown is set:
 * 2) Determine and then close the application that is preventing SQL Server from getting exclusive access to the database file.
 * 3) Stop and restart SQL Server to initiate recovery, or execute the following statement to recover the database without restarting:
 * 4) Verify that it is possible to access the database.