大网数据广告活动
数据库与应用安全

Transaction log file grows for databases with In-Memory OLTP in SQL Server 2022

· 52CE 我爱测

Troubleshoots the issue where transaction log file grows continuously for databases with In-Memory OLTP enabled in SQL Server 2022.

Symptoms

When your databases have the In-Memory OLTP feature enabled in SQL Server 2022, you notice the transaction log file grows continuously. In addition, the SQL Server error log might have messages like Close thread is falling behind: 4 checkpoints outstanding.

If you restart the SQL Server instance, you might notice the database takes a long time to complete the database recovery process.

Troubleshoot the issue with sys.databases and sys.dm_db_xtp_checkpoint_stats

  • When you use the catalog view sys.databases to gather information and troubleshoot this issue, the column log_reuse_wait_desc shows XTP_CHECKPOINT as the reason for long truncation. This value indicates that the transaction log is waiting for an In-Memory OLTP (formerly known as Hekaton) checkpoint to occur. It suggests a delay in checkpointing operations, potentially impacting performance or log file growth.

  • When you use the SQL Server dynamic management view (DMV) sys.dm_db_xtp_checkpoint_stats to gather information and troubleshoot this issue, the column outstanding_checkpoint_count shows a nonzero value for an extended period of time. It indicates that checkpoints aren't occurring efficiently, potentially affecting performance and log file growth.

Cause

SQL Server 2022 introduced new capabilities that improve memory management in large memory servers to reduce out-of-memory conditions. A known issue in this change can sometimes lead to the behavior described in the Symptoms section.

Resolution

To solve the issue, follow these steps:

  1. Add -T9810 as the startup parameter for the SQL Server instance.
  2. Restart the instance.
  3. Issue a checkpoint, take a log backup, observe log_reuse_wait_desc, and shrink the log if needed to reclaim space.

Note: This issue has been fixed in Cumulative Update 7 for SQL Server 2022.

More information

The trace flag 9810 disables the In-Memory OLTP engine from reclaiming Thread Local Storage (TLS) memory, reverting to the behavior of SQL Server 2019.

Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors (upstream author metadata: prmadhes-msft). Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.

资料来源与适用范围

资料来源
Microsoft Learn / IT Pro troubleshooting
适用范围
See source applicability · Source date 07/03/2023
内容语言
中文;命令、路径和错误标识保留技术原文
本地收录日期
2026-11-15
授权说明
CC BY 4.0 (documentation), MIT (code samples)

正文与所需资源由本站本地存储。查看来源及更新记录 ↗

请按适用版本和实际日志确认原因;本站没有对每一种系统或设备逐项执行文中的操作。