檔案狀態File States

適用於: 是SQL Server 是Azure SQL Database 是Azure Synapse Analytics (SQL DW) 是平行處理資料倉儲 APPLIES TO: yesSQL Server yesAzure SQL Database yesAzure Synapse Analytics (SQL DW) yesParallel Data Warehouse

SQL ServerSQL Server中,資料庫檔案的狀態是與資料庫的狀態分開維護。In SQL ServerSQL Server, the state of a database file is maintained independently from the state of the database. 檔案永遠處於特定狀態,例如 ONLINE 或 OFFLINE。A file is always in one specific state, such as ONLINE or OFFLINE. 若要檢視檔案目前的狀態,請使用 sys.master_filessys.database_files 目錄檢視。To view the current state of a file, use the sys.master_files or sys.database_files catalog view. 如果資料庫是離線的,就可以從 sys.master_files 目錄檢視來檢視檔案的狀態。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. 若要檢視檔案群組目前的狀態,請使用 sys.filegroups 目錄檢視。To view the current state of a filegroup, use the sys.filegroups catalog view. 如果檔案群組離線而您嘗試透過 Transact-SQLTransact-SQL 陳述式存取檔案群組,就會發生錯誤。If a filegroup is offline and you try to access the filegroup by a Transact-SQLTransact-SQL statement, it will fail with an error. 當查詢最佳化工具建立 SELECT 陳述式的查詢計畫時,它可避免使用離線檔案群組中所存在的非叢集索引和叢集檢視,以利陳述式成功。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. 不過,如果離線檔案群組包含目標資料表的堆積或叢集索引,SELECT 陳述式將會失敗。However, if the offline filegroup contains the heap or clustered index of the target table, the SELECT statements fail. 除此之外,在離線檔案群組中,以 INSERT、UPDATE 或 DELETE 陳述式修改含有索引的資料表將會失敗。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.

StateState 定義Definition
ONLINEONLINE 檔案可供所有的作業使用。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.
OFFLINEOFFLINE 檔案無法存取且可能不在磁碟上。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 state can 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. 如需有關還原單一檔案的詳細資訊,請參閱 RESTORE (Transact-SQL)For more information about restoring a single file, see RESTORE (Transact-SQL).

當資料庫處於完整或大量記錄復原狀態且檔案已捨棄,資料庫檔案也會設定 OFFLINE。A database file is also set OFFLINE when a database is in full or bulk logged recovery and a file is dropped. sys.master_files 中的項目會持續保存,直到過去 drop_lsn 值截斷交易記錄為止。The entry in sys.master_files persists until a transaction log is truncated past the drop_lsn value. 如需詳細資訊,請參閱交易記錄截斷For more information, see Transaction Log Truncation.
RESTORINGRESTORING 正在還原檔案。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 PENDINGRECOVERY 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. 如需詳細資訊,請參閱分次還原 (SQL Server)For more information, see Piecemeal Restores (SQL Server).
SUSPECTSUSPECT 在線上還原處理期間檔案復原失敗。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_LOSSDBCC CHECKDB with REPAIR_ALLOW_DATA_LOSS
DEFUNCTDEFUNCT 當它在線上時,就會卸除檔案。The file was dropped when it was not online. 當移除了離線檔案群組,在檔案群組中的所有檔案就會變成無用。All files in a filegroup become defunct when an offline filegroup is removed.

ALTER DATABASE (Transact-SQL)ALTER DATABASE (Transact-SQL)

資料庫狀態Database States

鏡像狀態 (SQL Server)Mirroring States (SQL Server)

DBCC CHECKDB (Transact-SQL)DBCC CHECKDB (Transact-SQL)

資料庫檔案與檔案群組Database Files and Filegroups