PRB: Missing Device Causes Database to Be Marked Suspect

• Microsoft SQL Server, versions 6.0 and 6.5

SYMPTOMS

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:

   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

   96/11/18 10:48:32.60 kernel   udactivate (primary): failed to open
   device C:\MSSQL\DATA\MSDB.DAT for vdn 127

   -or-

   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

   96/11/18 10:48:32.60 kernel   udactivate (primary): failed to open
   device C:\MSSQL\DATA\MSDB.DAT for vdn 127

   96/11/18 10:48:36.70 kernel   udread: Operating system error 6(The
   handle is invalid.) on device 'C:\MSSQL\DATA\MSDB.DAT' (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', 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.

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.

   select name, dbid, mode, status from sysdatabases where dbid =
   select db_id('msdb')

   name     dbid   mode   status
   ------------------------------

   msdb      5      0      328

   truncate log on chkpt
   database not recovered yet
   database is suspect

CAUSE

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.

WORKAROUND

To work around this problem, perform the steps below. Note that the final 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. 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, see the following article in the Microsoft Knowledge Base:

   ARTICLE-ID: Q165918
   TITLE     : INF: Bypass (Emergency) Mode and DUMP TRANSACTION WITH
               NO_LOG