You may encounter the following error messages in the Windows Application Event or the SQL Server Error log when SQL Server cannot start a database because it cannot open the database and/or transaction log files:
2010-03-27 14:21:34.65 spid52FileMgr::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.
You may encounter these errors during the SQL Server instance startup process or any Database operation that attempts to start the database [e.g. ALTER DATABASE]. In some scenarios, you could encounter both 17204 and 17207 errors and in other occasions you might just encounter one of them.
If a user database encounters these errors, that database will be left in the RECOVERY_PENDING state and applications cannot access this database. If a system database encounters these errors, the SQL Server instance will not start and you cannot connect to this instance of SQL Server. This could also result in SQL Server failover cluster resource to become offline.
Before any SQL Server database can be used, the database needs to be started. The database startup process involves initializing various data structures that represent the database and the database files, opening all the files that belong to the database, and finally performing recovery on the database. SQL Server uses Windows API CreateFile to open the files that belong to a database.
The error messages 17204 and 17207 indicate that an error was encountered while SQL Server attempted to open the database files during the startup process.
These error messages contain the following information:
Name of the SQL Server function that is attempting to open the file
The state information distinguishes multiple locations within a function that can generate this error message
The full physical path for the file
The File ID corresponding to the file
The Operating System error code and error description. In some instances only the error code will be provided.
The operating system error information printed in these error messages is the normal cause that results in these error messages. Common causes for these these error messages are a permission problem or a incorrect location or path problem.
resolving these errors 17204 and 17207 involve understanding the associated operating system error code and diagnosing that error. Once the operating system error condition is resolved, then you can attempt to restart the database (using for example ALTER DATABASE SET ONLINE) or the SQL Server instance to bring the affected database online. There could be scenarios where you cannot resolve the operating system error. In those cases, you have to take specific corrective actions. We will discuss more about these actions in this section.
If you are getting the "Access is Denied" operating system error consider these methods:
Check the permissions that are set of the file by looking at the properties of the file in Windows Explorer. SQL Server uses Windows groups to provision Access Control on the various file resources. Make sure the appropriate group [with names like SQLServerMSSQLUser$ComputerName$MSSQLSERVER or SQLServerMSSQLUser$ComputerName$InstanceName]has the required permissions on the database file that is mentioned in the error message. Review the Books Online topic "Setting Up Windows Service Accounts" for more details. Ensure that the Windows group actually includes the SQL Server service startup account or the service SID.
Review the user account under which the SQL Server service is currently running. You can use the Windows Task Manager to get this information. Look for the "User Name" value for the executable "sqlservr.exe". Also if you recently changed the SQL Server service account, please note that the only supported way to do this operation is to use the SQL Server Configuration Manager utility. More information on this is available at "SQL Server Configuration Manager".
Depending on the type of operation [opening databases during server startup, database attach, restore, etc] the user account that is used for impersonation and accessing the database file will vary. Review the topic "Securing Data and Log Files" to understand which operation sets what permission and to which accounts. Use a tool like Windows SysInternals "Process Monitor" to understand if the file access is happening under the security context of the SQL Server instance service startup account [or Service SID] or an impersonated account.
If you are getting the "The system cannot find the file specified" error message
Review the complete path from the error message
Ensure the disk drive and the folder path is visible and accessible from Windows Explorer
Review the Windows Event log to find out if any problems exist with this disk drive
If the path is incorrect and if this database already exists in the system, you can change the database file paths using the methods explained in the topic "Moving Database Files". You might have to use this procedure especially for system database files which encounter 17204 or 17207 and you are working through a disaster recovery scenario where the specified disk drives are unavailable. This topic also explains how you can identify the current location of the various system databases [master, model, tempdb, msdb and mssqlsystemresource].
If you are encountering this error because the database files are missing, you have to restore the database from a valid backup.
If the database file that encounters the error belongs to a secondary filegroup, then you can optionally mark that filegroup offline, bring the database online and then perform a restore of that filegroup alone. For more information, refer to the OFFLINE section of the topic "ALTER DATABASE File and Filegroup Options (Transact-SQL)".
If the file that is encountering a problem is a transaction log file, review the information under the sections "FOR ATTACH" and "FOR ATTACH_REBUILD_LOG" of the topic "CREATE DATABASE (Transact-SQL)" to understand how you can recreate the missing transaction log files.
Ensure that any disk or network location [like iSCSI drive] is available before SQL Server attempts to access the database files on these locations. If needed create the required dependencies in Cluster Administrator or Service Control Manager.
If you are getting the "The process cannot access the file because it is being used by another process" error
Use a tool like "Process Explorer" from Windows SysInternals to find out if another process or service has acquired exclusive lock on this database file
Stop that process from accessing SQL Server Database files
In a cluster environment, make sure that the sqlservr.exe process from the previous owning node has actually released the handles to the database files.
The function names you will normally observe in these error messages will be one of the following:
These names can help you identify if the file hat encounters an error while SQL Server attempts to open, is a database file [primary data file, files belonging to the primary file group, files belonging to the secondary file group, transaction log file] or a SQL FileStream container.
If the problem is related to your SQL Server FileStream filegroup, then you will notice that only the full directory path is listed instead of a file name. An example is shown below:
2010-03-27 15:25:14.32 spid52STREAMFCB::Startup: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQL2008\MSSQL\DATA\bpa_files_test_fs_1\bpa_files_test_fs_1'. Diagnose and correct the operating system error, and retry the operation.
If your error message refers to FCB::RemoveAlternateStreams, please refer to a known issue that can generate this message:
If SQL Server is impersonating the user credentials of the login which is performing the ALTER DATABASE or CREATE DATABASE operation, you will notice the following information in the Process Monitor tool from Windows SysInternals:
Options:Synchronous IO Non-Alert, Non-Directory File, Open No Recall
Microsoft SQL Server 2005 Developer Edition, Microsoft SQL Server 2005 Enterprise Edition, Microsoft SQL Server 2005 Express Edition, Microsoft SQL Server 2005 Standard Edition, Microsoft SQL Server 2005 Workgroup Edition, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Express, Microsoft SQL Server 2008 Express with Advanced Services, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Web, Microsoft SQL Server 2008 R2 Workgroup