Share via

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) 加密/解密失败，无法访问文件
    • 无法访问密钥保管库服务
    • EKM 提供程序模块遇到导致操作失败的异常、错误或其他问题
  • 由于对事务日志施加了大型事务、磁盘空间不足或文件大小限制，导致完整事务日志。 可在 9001 之前的SQL Server错误日志中找到错误 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) /Hardware Security Modules (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.