排查 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 Server 不同。

与 SQL Server 类似，每当成功完成日志备份时，每个数据库的事务日志都会被截断。 截断在日志文件中保留空白，之后日志文件可用于新事务。 当日志文件无法由日志备份截断时，日志文件会增长，以容纳新事务。 如果日志文件在 Azure SQL 数据库中增长到其最大限制，则新的写入事务会失败。

有关事务日志大小的信息，请参阅：

事务日志截断受阻

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

SELECT [name], log_reuse_wait_desc FROM sys.databases;

对于 Azure SQL 数据库，建议连接到特定的用户数据库而不是 master 数据库来执行此查询。

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

log_reuse_wait_desc 诊断 要求响应
无 典型状态。 没有任何内容阻止日志截断。 否。
CHECKPOINT 日志截断需要检查点。 罕见。 除非持续，否则无需响应。 如果持续，请向 Azure 支持提交支持请求。
日志备份 需要日志备份。 除非持续，否则无需响应。 如果持续，请向 Azure 支持提交支持请求。
主动备份或还原 数据库备份正在进行。 除非持续，否则无需响应。 如果持续，请向 Azure 支持提交支持请求。
活动事务 正在进行的事务在阻止日志截断。 由于活动事务和/或未提交的事务，无法截断日志文件。 请参阅下一节。
复制 在 Azure SQL 数据库中，如果启用了更改数据捕获 (CDC)，则可能会发生这种情况。 查询 sys.dm_cdc_errors 并解决错误。 如果无法解决，请向 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
, azure_dtc_state    --Applies to: Azure SQL Database only
             =    CASE tat.dtc_state 
                 WHEN 1 THEN 'ACTIVE'
                 WHEN 2 THEN 'PREPARED'
                 WHEN 3 THEN 'COMMITTED'
                 WHEN 4 THEN 'ABORTED'
                 WHEN 5 THEN 'RECOVERED' END
, 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 数据库中数据库的文件空间。

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

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

若要解决此问题，请尝试执行以下方法：

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

注意

有关其他资源治理错误的详细信息，请参阅资源治理错误。

后续步骤