MSSQLSERVER_9001

發行項
08/21/2023

適用於：SQL Server

詳細資料

屬性	值
產品名稱	SQL Server
事件識別碼	9001
事件來源	MSSQLSERVER
元件	SQLEngine
符號名稱	LOG_NOT_AVAIL
訊息文字	無法使用資料庫 '%.*ls' 的記錄檔。相關錯誤訊息請查閱事件記錄檔。解決任何錯誤，並重新啟動資料庫。

說明

當資料庫記錄檔無法使用時，就會發生錯誤 9001。當資料庫記錄離線時，表示發生嚴重失敗，導致資料庫中發生交易。這類失敗需要重新開機資料庫，或讓您還原備份。錯誤會顯示最終結果，但不會說明導致此狀態的結果。有些其他問題導致記錄檔無法使用，您必須調查基礎問題。以下是 SQL 錯誤記錄檔中錯誤顯示方式的範例

Error: 9001, Severity: 21, State: 5.
The log for database 'ContosoDb' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.

通常錯誤 9001 會與其他錯誤一起發生，以提供更具體的根本原因說明。範例包括錯誤 9002 、 3313 、 3314、 17204 （在存取檔案時顯示 OS 錯誤）、 17053 （顯示 OS 錯誤）、 823 。

在某些情況下，SQL Server 會在執行時間嘗試重新開機資料庫並執行復原，或者可能會自行重新開機（整個服務）。如果自動資料庫重新開機失敗或未發生，您可能會嘗試重新開機 SQL Server，並查看資料庫的復原是否成功使資料庫上線。如果沒有，您必須解決交易記錄無法使用的根本原因。這是顯示資料庫重新開機的錯誤訊息 3422 範例：

Database ContosoDb was shutdown due to error 9001 in routine 'XdesRMFull::CommitInternal'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.

下列訊息指出即將重新開機 SQL Server：

Error: 3449, Severity: 21, State: 1.
SQL Server must shut down in order to recover a database (database ID 2). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

原因

資料庫交易記錄可能會因為許多原因而無法使用。一些範例包括

交易記錄檔位於失敗或無法使用的存放裝置上
實體損毀的交易記錄檔，導致無法寫入或讀取記錄檔
無法透過透明資料加密存取檔案，因為加密/解密失敗（TDE）
- 無法存取金鑰保存庫服務
- EKM 提供者模組會執行到例外狀況、錯誤或其他導致無法成功作業的問題
由於大型交易、磁碟空間不足或交易記錄上強加的檔案大小限制，所以完整交易記錄檔。錯誤 9002 可能會在 9001 之前的 SQL Server 錯誤記錄檔中找到。如需詳細資訊，請參閱 MSSQLSERVER_9002

使用者動作

先解決 9001 之前的錯誤。然後，如果尚未發生，則嘗試重新開機 SQL Server 實例以復原資料庫。

解決完整的交易記錄錯誤

您可能會在錯誤 9001 之前發現錯誤 9002。以下為範例：

Error: 9002, Severity: 17, State: 9.
The transaction log for database 'ContosoDb' is full due to 'AVAILABILITY_REPLICA'.
Error: 3314, Severity: 21, State: 3.
During undoing of a logged operation in database 'ContosoDb' (page (1:32573799) if any), an error occurred at log record ID (7672713:36228:159). Typically, the specific failure is logged previously as an error in the operating system error log. Restore the database or file from a backup, or repair the database.
State information for database 'ContosoDb' - Hardened Lsn: '(7672713:38265:1)'    Commit LSN: '(7672712:1683087:46)'    Commit Time: 'Jul  1 2021  5:51AM'
Database ContosoDb was shutdown due to error 3314 in routine 'XdesRMReadWrite::RollbackToLsn'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.
Always On Availability Groups connection with secondary database terminated for primary database 'ContosoDb' on the availability replica 'PRDAT1ANLYSQL05' with Replica ID: {38a71ff9-f0ee-4737-9255-bb6a73e1c5d5}. This is an informational message only. No user action is required.
Error during rollback. shutting down database (location: 1).

Error: 9001, Severity: 21, State: 5.
The log for database 'ContosoDb' is not available. Check the operating system error log for related error messages. Resolve any errors and restart the database.

Recovery of database 'ContosoDb' (6) is 0% complete (approximately 60466 seconds remain). Phase 2 of 3. This is an informational message only. No user action is required.

在這種情況下，請專注于解決根本原因 - 完整交易記錄

若要解決完整的交易記錄，請參閱針對完整交易記錄進行疑難排解（SQL Server 錯誤 9002）

請確定您釋放交易記錄空間，並找出為何未釋出事務歷史記錄空間
釋放交易記錄所在的磁碟空間
如有需要，請展開現有的記錄檔，或視需要新增記錄檔

視需要解決硬體和 OS 問題，並從備份還原

通常錯誤 9001 發生在交易記錄檔損毀或因為儲存體裝置問題而使記錄檔無法使用時發生。以下是您可能會觀察到的兩個錯誤範例：

例如，儲存體磁片區無法使用，且 OS 傳回錯誤「裝置尚未就緒」。您可以看到磁片因無法使用而損毀的其他錯誤。這些範例提供內容，讓您瞭解錯誤 9001 只是較大問題的許多徵兆之一。

Error: 823, Severity: 24, State: 2.
The operating system returned error 21(The device is not ready.) to SQL Server during a read at offset 0x000009afde6000 in file 'G:\Data\Files\ContosoDb_4.ldf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

Error: 9001, Severity: 21, State: 3.
The log for database 'ContosoDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Database ContosoDb was shutdown due to error 9001 in routine 'XdesRMFull::CommitInternal'. Restart for non-snapshot databases will be attempted after all connections to the database are aborted.

Starting up database 'ContosoDb'.
Error: 17204, Severity: 16, State: 1.
FCB::Open failed: Could not open file G:\Data\Files\ContosoDb.mdf for file number 1.  OS error: 3(The system cannot find the path specified.).
Error: 5120, Severity: 16, State: 101.
Unable to open the physical file "G:\Data\Files\ContosoDb.mdf". Operating system error 3: "3(The system cannot find the path specified.)".
Error: 17207, Severity: 16, State: 1.
FileMgr::StartPrimaryDataFiles: Operating system error 2(The system cannot find the file specified.) occurred while creating or opening file 'G:\Data\Files\ContosoDb_0.ndf'. Diagnose and correct the operating system error, and retry the operation.

以下是另一個範例，其中 OS 會報告導致多個資料庫無法存取交易記錄的裝置錯誤：

Error: 17053, Severity: 16, State: 1.
SQLServerLogMgr::LogWriter: Operating system error 1117(The request could not be performed because of an I/O device error.) encountered.


Error: 9001, Severity: 21, State: 4.
The log for database 'ContosoDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Always On Availability Groups data movement for database 'ContosoDb' has been suspended for the following reason: "system" (Source ID 2; Source string: 'SUSPEND_FROM_REDO'). To resume data movement on the database, you will need to resume the database manually. For information about how to resume an availability database, see SQL Server Books Online.


Error: 9001, Severity: 21, State: 16.
The log for database 'tempdb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.
Error: 3449, Severity: 21, State: 1.
SQL Server must shut down in order to recover a database (database ID 2). The database is either a user database that could not be shut down or a system database. Restart SQL Server. If the database fails to recover after another startup, repair or restore the database.

同時，Windows 系統事件記錄檔會報告存放裝置錯誤：

Warning       NODEDB1 129     pvscsi     Reset to device, \Device\RaidPort2, was issued.
Warning       NODEDB1 153     Disk       The IO operation at logical block address 0xxxxxxxx for Disk 4 (PDO name: \Device\0000007f) was retried.

若要解決這類問題：

確定資料庫和記錄檔所在的儲存體磁片區已上線、從電腦到儲存體的整個 I/O 路徑是穩定且可靠的，而且不會造成實體檔案損毀。
請與您的硬體和裝置製造商合作，以確保硬體及其設定適用于資料庫系統的 I/O 需求。確定 I/O 路徑中的設備磁碟機、韌體、BIOS 和其他支援軟體元件是最新的。
執行 DBCC CHECKDB 來檢查資料庫的一致性，如果在重新開機後可以上線
如果資料庫和記錄檔未完整，因此資料庫無法上線，請還原資料庫的最後一個已知良好備份
如需疑難排解建議，請參閱 MSSQLSERVER 錯誤 823 和針對 DBCC CHECKDB 報告的資料庫一致性錯誤進行疑難排解

解決 TDE 加密或描述失敗

如果您使用外部可延伸金鑰管理（EKM）/硬體安全性模組（HSM）服務或提供者，請確定服務所提供的模組穩定且更新。請與 EKM/HSM 提供者廠商合作，解決執行檔案加密/解密之模組的任何問題。

當此問題發生時，您可能會在 SQL 錯誤記錄檔中觀察到下列徵兆：

**Dump thread - spid = 0, EC = 0x0000023FDA293320
***Stack Dump being sent to F:\Data\MSSQL13.INST1\MSSQL\LOG\SQLDump0007.txt
* *******************************************************************************
*
* BEGIN STACK DUMP:
*   11/22/22 12:04:58 spid 1
*
* Crypto Exception
*

00007FFBA0C81791 Module(ntdll+0000000000051791)
Stack Signature for the dump is 0x00000000D3AC1708
External dump process return code 0x20000001.  External dump process returned no errors.

Error: 15466, Severity: 16, State: 28.
An error occurred during decryption.
Error: 9001, Severity: 21, State: 16.
The log for database 'ContosoDb' is not available. Check the event log for related error messages. Resolve any errors and restart the database.

Share via