通过

MSSQLSERVER_9001

  • 项目

适用范围：SQL Server

详细信息

Attribute 值
产品名称 SQL Server
事件 ID 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）加密/解密失败访问文件
    • Key Vault 服务无法访问
    • EKM 提供程序模块遇到异常、错误或其他阻止成功操作的问题
  • 由于事务日志存在大型事务、磁盘空间不足或文件大小限制，因此事务日志已满。 可能在 SQL Server 日志中在 9001 之前找到错误 9002。 有关详细信息，请参阅 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.