PRB: DB Marked Suspect; Operating System Error 5 (Access Denied)

ID: Q193453

The information in this article applies to:

Microsoft SQL Server version 6.5

SYMPTOMS

After you log in to SQL Server, the database is marked suspect. The error log contains:


   96/11/11 18:38:19.57 spid1    Activating disk '%*.s'
   96/11/11 18:38:19.57 kernel   udopen: operating system error 5(Access is
   denied.) during the creation/opening of physical device
   &lt;Pysical_Device_Path&gt;
   96/11/11 18:38:19.57 kernel   udactivate (primary): failed to open
   device &lt;Pysical_Device_Path&gt; for vdn %*.d


   96/11/11 18:38:23.35 kernel   udread: Operating system error 6(The
   handle is invalid.) on device '&lt;Physical_Device_Path&gt;' (virtpage %*.d).
   96/11/11 18:38:23.37 spid15   Error : 840, Severity: 17, State: 2
   96/11/11 18:38:23.37 spid15   Device '%*.s' (with physical name
   '&lt;Physical_Device_Path&gt;', and virtual device number %*.d) is not
   available.  Please contact System Administrator for assistance.
   96/11/11 18:38:23.37 spid15   Buffer %*.d from database '%*.s' has page
   number %*.d in the page header and page number %*.d in the buffer header
   96/11/11 18:38:23.39 spid15   Unable to proceed with the recovery of
   dbid &lt;%*.d&gt; because of previous errors.  Continuing with the next
   database.

Subsequent attempts to start SQL Server cause the following text to be written in the error log:


   96/11/11 19:03:18.98 spid12   Database '%*.s' cannot be opened - it has
   been marked SUSPECT by recovery. The SA can drop the database with DBCC.
   96/11/11 19:03:18.98 spid12   Unable to proceed with the recovery of
   dbid &lt;%*.d&gt; because of previous errors.  Continuing with the next
   database.

NOTE: If the database in question is master, the behavior is slightly different, in that the master database is not marked suspect, but SQL Server fails to start.

CAUSE

The account under which the MSSQLServer service is running does not have sufficient privileges on the device file.

WORKAROUND

To work around this problem, perform the following steps:

Stop the MSSQLServer and SQLExecutive services.

Check the permissions for the device file, the directory it is in, and the volume it is on. If the MSSQLServer service is running under the "LocalSystem" account, then "System" should have Full Control over the volume, directory, and file. Make sure the device file's attribute is not set to Read Only.

If the database you were trying to use is the master database, you should now be able to start SQL Server normally. If the database you are trying to use is not the master database, and it has been marked suspect, proceed to step 3 below.

Because the database has been marked suspect, its status must be reset. To do this, perform the following steps:
1. 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.
2. Execute sp_resetstatus in the master database for the suspect database:
  use master
  go
  sp_resetstatus <db_name>
3. Stop and restart SQL Server.
4. Verify that the database was recovered and is available.
5. Run DBCC NEWALLOC, DBCC TEXTALL, and DBCC CHECKDB.

MORE INFORMATION

For additional information, please see the following article in the Microsoft Knowledge Base:

Q180500 PRB: Missing Device Causes Database to Be Marked Suspect

Additional query words: prodsql dev db

Keywords :
Version : winnt:6.5
Platform : winnt
Issue type : kbprb