How to troubleshoot SQL Server Errors that prevent a successful database startup or recovery

Koskee seuraavia: SQL Server 2008 EnterpriseSQL Server 2008 R2 Enterprise

Symptoms


You may encounter one or more of the following messages when starting up a database:

2010-03-27 14:21:34.58 spid52      Error: 17204, Severity: 16, State: 1. 2010-03-27 14:21:34.58 spid52      FCB::Open failed: Could not open file c:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\data\MyDB_Prm.mdf for file number 1.  OS error: 5(Access is denied.).

2010-03-27 14:21:34.65 spid52      Error: 17207, Severity: 16, State: 1. 2010-03-27 14:21:34.65 spid52      FileMgr::StartSecondaryDataFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'F:\MSSQL\DATA\MyDB_FG1_1.ndf'. Diagnose and correct the operating system error, and retry the operation.

Error: 9004, Severity: 21, State: 1.
An error occurred while processing the log for database 'mydb'.  If possible, restore from backup. If a backup is not available, it might be necessary to rebuild the log.

Error: 3414, Severity: 21, State: 1.
An error occurred during recovery, preventing the database 'mydb' (database ID 13) from restarting. Diagnose the recovery errors and fix them, or restore from a known good backup. If errors are not corrected or expected, contact Technical Support)



Cause


These messages will be reported due to various reasons. The possible causes for each one of these error messages is discussed in the articles referenced in the Resolution section.

Resolution


More Information


For more information about the products or tools that automatically check for this condition on your instance of SQL Server and on the versions of the SQL Server product, see the following table:
Rule software

Rule title

Rule description

Product versions against which the rule is evaluated
SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA)





Database availability errors detected in this instance of SQL






The SQL Server 2008 R2 Best Practice Analyzer (SQL Server 2008 R2 BPA) provides a rule to detect situations where databases encountered errors [17204, 17207, 9004, and 3414] during the startup or recovery process. The SQL Server 2008 R2 BPA supports both SQL Server 2008 and SQL Server 2008 R2. 

If you run the BPA tool and encounter an Error with the title of Database Engine - Database availability errors detected in this instance of SQL Server, then you need to follow the recommendations from the resolution section of this article. You have to examine each instance of the errors and perform necessary corrective action.
SQL Server 2008
SQL Server 2008 R2







SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA)





Database availability errors detected in this instance of SQL





The SQL Server 2012 Best Practice Analyzer (SQL Server 2012 BPA) provides a rule to detect situations where databases encountered errors [17204, 17207, 9004, and 3414] during the startup or recovery process.

If you run the BPA tool and encounter an Error with the title of Database Engine - Database availability errors detected in this instance of SQL Server, then you need to follow the recommendations from the resolution section of this article. You have to examine each instance of the errors and perform necessary corrective action.
SQL Server 2012