排查 Azure SQL 托管实例的事务日志错误

  • 项目

适用于：Azure SQL 托管实例

事务日志已满且无法接受新事务时，你可能会看到错误 9002 或 40552。 当由 Azure SQL 托管实例管理的数据库事务日志超过空间阈值且无法继续接受事务时，便会发生这些错误。 这些错误类似于 SQL Server 中事务日志已满的问题，但在 SQL Serve、Azure SQL 数据库和 Azure SQL 托管实例中具有不同的解决方案。

注意

本文重点介绍 Azure SQL 托管实例。 Azure SQL 托管实例基于最新稳定版本的 Microsoft SQL Server 数据库引擎，因此很多内容是相似的，尽管故障排除选项和工具可能与 SQL Server 不同。

有关排查 Azure SQL 数据库中的事务日志问题的详细信息，请参阅排查 Azure SQL 数据库的事务日志错误。

有关排除 SQL Server 中事务日志故障的更多信息，请参阅排除完整事务日志故障 (SQL Server Error 9002)。

自动备份和事务日志

在 Azure SQL 托管实例中，会自动创建事务日志备份。 有关频率、保留期和更多信息，请参阅自动备份。 若要跟踪何时在 SQL 托管实例上执行自动备份，请查看监视备份活动。

无法管理数据库文件的位置和名称，但管理员可以管理数据库文件和文件自动增长设置。 事务日志问题的常见原因和解决方法类似于 SQL Server。

与 SQL Server 类似，每当成功完成日志备份时，每个数据库的事务日志都会被截断。 日志截断会从事务日志中删除非活动的虚拟日志文件 (VFS)，释放文件内部的空间，但不更改磁盘上的文件大小。 然后，日志文件中的空白空间可用于新事务。 当日志文件无法由日志备份截断时，日志文件会增长，以容纳新事务。 如果日志文件在 Azure SQL 托管实例中增长到其上限，则新的写入事务会失败。

在 Azure SQL 托管实例中，可以独立于计算购买附加存储，但存在上限。 有关详细信息，请参阅管理文件以释放更多空间。

事务日志截断受阻

若要发现在特定情况下阻止日志截断的内容，请参阅 sys.databases 中的 log_reuse_wait_desc。 日志重复使用等待会告知哪些情况或原因会阻止常规日志备份截断事务日志。 有关详细信息，请参阅 sys.databases (Transact-SQL)。

SELECT [name], log_reuse_wait_desc FROM sys.databases;

sys.databases 中 log_reuse_wait_desc 的以下值可能表示阻止数据库事务日志截断的原因：

log_reuse_wait_desc 诊断 要求响应
无 典型状态。 没有任何内容阻止日志截断。 否。
CHECKPOINT 日志截断需要检查点。 罕见。 除非持续，否则无需响应。 如果持续，请向 Azure 支持提交支持请求。
日志备份 需要日志备份。 除非持续，否则无需响应。 如果持续，请向 Azure 支持提交支持请求。
主动备份或还原 数据库备份正在进行。 除非持续，否则无需响应。 如果持续，请向 Azure 支持提交支持请求。
活动事务 正在进行的事务在阻止日志截断。 由于活动事务和/或未提交的事务，无法截断日志文件。 请参阅下一节。
REPLICATION 在 Azure SQL 托管实例中，如果启用了复制或 CDC，则可能会发生。 如果持续，请调查与 CDC 或复制相关的代理。 如需对 CDC 进行故障排除，请查询 msdb.dbo.cdc_jobs 中的作业。 如果不存在，请通过 sys.sp_cdc_add_job 添加。 对于复制，请参阅排查事务复制问题。 如果无法解决，请向 Azure 支持人员提交支持请求。
AVAILABILITY_REPLICA 正在同步到次要副本。 除非持续，否则无需响应。 如果持续，请向 Azure 支持提交支持请求。

活动事务阻止的日志截断

对于不能接受新事务的事务日志，最常见的情况是长时间运行或受阻的事务。

运行此示例查询以查找未提交的事务或活动事务及其属性。

  • 从 sys.dm_tran_active_transactions 返回有关事务属性的信息。
  • 从 sys.dm_exec_sessions 返回会话连接信息。
  • 从 sys.dm_exec_requests 返回请求信息（活动请求）。 此查询还可用于标识受阻止的会话，查找 request_blocked_by。 有关详细信息，请参阅收集阻止信息。
  • 使用 sys.dm_exec_sql_text 或 sys.dm_exec_input_buffer DMV 返回当前请求的文本或输入缓冲区文本。 如果 sys.dm_exec_sql_text 的 text 字段返回的数据为 NULL，则表示请求未处于活动状态，但具有未完成的事务。 在这种情况下，sys.dm_exec_input_buffer 的 event_info 字段包含传递给数据库引擎的最后一条语句。
SELECT [database_name] = db_name(s.database_id)
, tat.transaction_id, tat.transaction_begin_time, tst.session_id 
, session_open_transaction_count = tst.open_transaction_count
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime())
, input_buffer = ib.event_info
, request_text = CASE  WHEN r.statement_start_offset = 0 and r.statement_end_offset= 0 THEN left(est.text, 4000)
                       ELSE    SUBSTRING ( est.[text],    r.statement_start_offset/2 + 1, 
                                           CASE WHEN r.statement_end_offset = -1 THEN LEN (CONVERT(nvarchar(max), est.[text])) 
                                                ELSE r.statement_end_offset/2 - r.statement_start_offset/2 + 1
                                           END  )  END
, request_status = r.status
, request_blocked_by = r.blocking_session_id
, transaction_state = CASE tat.transaction_state    
                     WHEN 0 THEN 'The transaction has not been completely initialized yet.'
                     WHEN 1 THEN 'The transaction has been initialized but has not started.'
                     WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
                     WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
                     WHEN 4 THEN 'The commit process has been initiated on the distributed transaction. This is for distributed transactions only. The distributed transaction is still active but further processing cannot take place.'
                     WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
                     WHEN 6 THEN 'The transaction has been committed.'
                     WHEN 7 THEN 'The transaction is being rolled back.'
                     WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name
, transaction_type = CASE tat.transaction_type    WHEN 1 THEN 'Read/write transaction'
                                             WHEN 2 THEN 'Read-only transaction'
                                             WHEN 3 THEN 'System transaction'
                                             WHEN 4 THEN 'Distributed transaction' END
, tst.is_user_transaction
, local_or_distributed = CASE tst.is_local WHEN 1 THEN 'Local transaction, not distributed' WHEN 0 THEN 'Distributed transaction or an enlisted bound session transaction.' END
, transaction_uow    --for distributed transactions. 
, s.login_time, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
, session_cpu_time = s.cpu_time, session_logical_reads = s.logical_reads, session_reads = s.reads, session_writes = s.writes
, observed = sysdatetimeoffset()
FROM sys.dm_tran_active_transactions AS tat 
INNER JOIN sys.dm_tran_session_transactions AS tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions AS s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests AS r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib 
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) AS est;

文件管理以释放更多空间

如果事务日志被阻止在 Azure SQL 托管实例中截断，则释放空间可能是解决方案的一部分。 但是，解决阻止事务日志文件截断的根本原因是关键。 在某些情况下，临时创建更多磁盘空间可让长时间运行的事务完成，从而消除正常事务日志备份截断事务日志文件受阻的情况。 但是，释放空间也许只能在事务日志再次增长之前提供暂时的缓解。

在 Azure SQL 托管实例中，可以独立于计算购买附加存储，但存在上限。 例如，在 Azure 门户中，访问“计算 + 存储”页，以增加存储量 (GB)。 有关事务日志大小限制的信息，请参阅 SQL 托管实例的资源限制。 有关详细信息，请参阅管理 Azure SQL 托管实例中数据库的文件空间。

不会从 SQL 托管实例存储空间中扣除备份存储。 备份存储与实例存储空间无关，其大小不受限制。

错误 9002：数据库的事务日志已满

9002: The transaction log for database '%.*ls' is full. To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases.

错误 9002 发生在 SQL Server 和 Azure SQL 托管实例中，原因相同。

对已满事务日志的正确响应取决于导致日志已满的情况。

要解决错误 9002，请尝试以下方法：

  • 事务日志未被截断，并且已增长以填充所有可用空间。
    • 由于 Azure SQL 托管实例中的事务日志备份是自动的，因此其他操作必须阻止事务日志活动被截断。 不完整的复制、CDC 或可用性组同步可能会阻止截断，请参阅“阻止的事务日志截断”。
  • SQL 托管实例预留存储大小已满，事务日志无法增长。
  • 事务日志大小设为了固定的最大值，或自动增长已禁用，因此无法增长。

错误 40552：由于过度使用事务日志空间，已终止会话

40552: The session has been terminated because of excessive transaction log space usage. Try modifying fewer rows in a single transaction.

虽然在 Azure SQL 托管实例中，错误 9002 比错误 40552 更常见，但两者都可能发生。

要解决错误 40552，请尝试以下方法：

  • 该问题可能发生在任何 DML 操作中，例如插入、更新或删除。 查看事务以避免不必要的写入。 通过立即实施批处理或拆分为多个较小的事务，来尝试减少操作的行数。 有关详细信息，请参阅如何使用批处理来提升应用程序的性能。
  • 此问题可能是索引重建操作造成的。 若要避免此问题，请确保以下公式为 true：（表中受影响的行数）乘以（更新的字段的平均大小，以字节为单位 + 80）< 2 GB。 对于大型表，请考虑仅对表的一些分区创建分区和执行索引维护。 有关详细信息，请参阅创建分区表和索引。
  • 如果在使用 bcp.exe 实用程序或 System.Data.SqlClient.SqlBulkCopy 类执行大容量插入，则可尝试使用 -b batchsize 或 BatchSize 选项来限制在各事务中复制到服务器的行数。 有关详细信息，请参阅 bcp Utility。
  • 如果使用 ALTER INDEX 语句重新生成索引，请使用 SORT_IN_TEMPDB = ON、ONLINE = ON 和 RESUMABLE=ON 选项。 使用可恢复索引时，日志截断更频繁。 有关详细信息，请参阅 ALTER INDEX (Transact-SQL)。

后续步骤