File States
In SQL Server, the state of a database file is maintained independently from the state of the database. A file is always in one specific state, such as ONLINE or OFFLINE. To view the current state of a file, use the sys.master_files or sys.database_files catalog view. If the database is offline, the state of the files can be viewed from the sys.master_files catalog view.
The state of the files in a filegroup determines the availability of the whole filegroup. For a filegroup to be available, all files within the filegroup must be online. To view the current state of a filegroup, use the sys.filegroups catalog view. If a filegroup is offline and you try to access the filegroup by a Transact-SQL statement, it will fail with an error. When the query optimizer builds query plans for SELECT statements, it avoids nonclustered indexes and indexed views that reside in offline filegroups, letting these statements to succeed. However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. Additionally, any INSERT, UPDATE, or DELETE statement that modifies a table with any index in an offline filegroup will fail.
File State Definitions
The following table defines the file states.
| State | Definition |
|---|---|
| ONLINE | The file is available for all operations. Files in the primary filegroup are always online if the database itself is online. If a file in the primary filegroup is not online, the database is not online and the states of the secondary files are undefined. |
| OFFLINE | The file is not available for access and may not be present on the disk. Files become offline by explicit user action and remain offline until additional user action is taken. ** Caution ** A file should only be set offline when the file is corrupted, but it can be restored. A file set to offline can only be set online by restoring the file from backup. For more information about restoring a single file, see RESTORE (Transact-SQL). |
| RESTORING | The file is being restored. Files enter the restoring state because of a restore command affecting the whole file, not just a page restore, and remain in this state until the restore is completed and the file is recovered. |
| RECOVERY PENDING | The recovery of the file has been postponed. A file enters this state automatically because of a piecemeal restore process in which the file is not restored and recovered. Additional action by the user is required to resolve the error and allow for the recovery process to be completed. For more information, see Piecemeal Restores (SQL Server). |
| SUSPECT | Recovery of the file failed during an online restore process. If the file is in the primary filegroup, the database is also marked as suspect. Otherwise, only the file is suspect and the database is still online. The file will remain in the suspect state until it is made available by one of the following methods: Restore and recovery DBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS |
| DEFUNCT | The file was dropped when it was not online. All files in a filegroup become defunct when an offline filegroup is removed. |