Select the product you need help with
- Internet Explorer
- Windows Phone
- More products
Missing device causes database to be marked suspect
Article ID: 180500 - View products that this article applies to.
This article was previously published under Q180500
SQL Server marks a database suspect if any of the device files for the database are unavailable when it attempts to start. You may see either of the following sets of messages in the SQL Server error log:
Error message 1
96/11/18 10:48:32.60 kernel 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,
C:\DATA\SQL\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF)
96/11/18 10:48:32.60 kernel udactivate (primary): failed to open
device C:\MSSQL\DATA\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF) for vdn 127
Error message 2
These will be followed later in the log by:
96/11/18 10:48:32.60 kernel udopen: operating system error 2(The
system cannot find the file specified.) during the creation/opening
of physical device C:\MSSQL\DATA\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF)
96/11/18 10:48:32.60 kernel udactivate (primary): failed to open device C:\MSSQL\DATA\MSDB.DAT (or C:\DATA\SQL\MSDB.MDF) for vdn 127
For example, performing the following steps will demonstrate the problem:
96/11/18 10:48:36.70 kernel udread: Operating system error 6(The
handle is invalid.) on device 'C:\MSSQL\DATA\MSDB.DAT' (or C:\DATA\SQL\MSDB.MDF) (virtpage
96/11/18 10:48:36.77 spid11 Error : 840, Severity: 17, State: 2
96/11/18 10:48:36.77 spid11 Device 'MSDBData' (with physical name
'C:\MSSQL\DATA\MSDB.DAT' (or C:\DATA\SQL\MSDB.MDF), and virtual device number 127) is not
available. Please contact System Administrator for assistance.
96/11/18 10:48:36.77 spid11 Buffer 1092480 from database 'msdb'
has page number 0 in the page header and page number 24 in the
96/11/18 10:48:37.43 spid11 Unable to proceed with the recovery of
dbid <5> because of previous errors. Continuing with the next
You receive the following results:
name dbid mode status ------------------------------ msdb 5 0 328
The status of 328 evaluates to:
truncate log on chkpt database not recovered yet database is suspect
For more information, see the "Sysdatabases (Master Database Only)" topic in the SQL Server Books Online.
At startup, SQL Server attempts to obtain an exclusive lock on the device file. If the device is being used by another process (for example, backup software) or if the file is missing, the scenario described above will be encountered. In these cases, there is usually nothing wrong with the devices and database. For the database to recover correctly, the device must be made available, and the database status must be reset.
To work around this problem, perform the steps below. Note that the final step is critical.
If the database is still marked as suspect after performing these steps, there may be other problems preventing the database from recovering. At this point, you can either restore from a good backup or set the database to emergency mode and use the bulk copy program (BCP) to bulk copy the data out. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
165918Important If you use this article (Q165918) and are unsure of the full consequences of any of the actions you are performing, contact your primary support provider for assistance.
(http://support.microsoft.com/kb/165918/ )Bypass (Emergency) mode and DUMP TRANSACTION WITH NO_LOG
Article ID: 180500 - Last Review: May 30, 2006 - Revision: 5.0