Missing device causes database to be marked suspect

This article was previously published under Q180500
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
SQL Server marks a database suspect if any of the device files for thedatabase are unavailable when it attempts to start. You may see either ofthe 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
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
These will be followed later in the log by:
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
0x7f000018).

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
buffer header

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
database.
For example, performing the following steps will demonstrate the problem:
  1. Stop SQL Server.
  2. Issue the following command from a command prompt at the Mssql\Data directory:

    ren msdb.dat msdb.sav
  3. Start SQL Server.
You will see the above errors (the ones from the second set) in the SQLServer errorlog. If you then issue the following query in the masterdatabase
   select name, dbid, mode, status from sysdatabases where dbid =   db_id('msdb')				

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)" topicin the SQL Server Books Online.
CAUSE
At startup, SQL Server attempts to obtain an exclusive lock on the devicefile. If the device is being used by another process (for example, backupsoftware) or if the file is missing, the scenario described above will beencountered. In these cases, there is usually nothing wrong with thedevices and database. For the database to recover correctly, the devicemust be made available, and the database status must be reset.
WORKAROUND
To work around this problem, perform the steps below. Note that thefinal step is critical.
  1. Ensure that the device file is actually available.
  2. Use the supplemental stored procedure sp_resetstatus to reset the status of a suspect database. If you have not already done so, create this procedure by executing the Instsupl.sql script, found in the Mssql\Install directory. For more information on sp_resetstatus, see the "Resetting the Suspect Status" topic in the SQL Server Books Online.
  3. Execute sp_resetstatus in the master database for the suspect database:
          use master      go      exec sp_resetstatus msdb   -- replace msdb with your database name 						
    You will see the following output:
          Prior to Update sysdatabases attempt for DBName='msdb', the mode=0      and status=328 (status suspect_bit=256). For DBName='msdb' in      sysdatabases, status bit 256 was forced Off and mode was forced to      0. WARNING: You MUST stop/restart SQL Server prior to accessing this      database! 					
  4. Stop and restart SQL Server.
  5. Verify that the database was recovered and is available.
  6. Run DBCC NEWALLOC, DBCC TEXTALL, and DBCC CHECKDB.
MORE INFORMATION
If the database is still marked as suspect after performing these steps,there may be other problems preventing the database from recovering. Atthis point, you can either restore from a good backup or set the databaseto emergency mode and use the bulk copy program (BCP) to bulk copy the dataout. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
165918 Bypass (Emergency) mode and DUMP TRANSACTION WITH NO_LOG
Important If you use this article (Q165918) and are unsure of the fullconsequences of any of the actions you are performing, contact your primarysupport provider for assistance.

db checkpoint
Properties

Article ID: 180500 - Last Review: 12/05/2015 08:22:00 - Revision: 5.0

Microsoft SQL Server 6.0 Standard Edition, Microsoft SQL Server 6.5 Standard Edition

  • kbnosurvey kbarchive kbprb KB180500
Feedback