SQLSERVER2014 使用内存表之后日志无法截断

Question

各位大神：目前症状是每当CheckPoint的时候，日志文件就满了，然后自动增长，一直在增长，求救。

当查询 sys.databases 目录视图时，数据库的log_reuse_wait_desc列显示值为XTP_CHECKPOINT。这意味着该日志截断正在等待一个检查点。但是，即使发出多个检查点之后， log_reuse_wait_desc列仍显示XTP_CHECKPOINT。

Answer 1

Hi 三个小圈圈,

请给SQL Server 2014更新到SP1 CU4，这个问题已经在这个更新中修复了。

Best Regards,

Teige

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 2

Hi 三个小圈圈,

请给SQL Server 2014更新到SP1 CU4，这个问题已经在这个更新中修复了。

Best Regards,

Teige

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

您好！目前的数据库版本已经是SP2了，如下：

Microsoft SQL Server 2014 (SP2) (KB3171021) - 12.0.5000.0 (X64) 

Answer 3

Hi 三个小圈圈,

尝试一下手动checkpoint会不会出错，另外有定期做backup吗，恢复模式是什么？

Best Regards,

Teige

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 4

恢复模式是完整模式，现在就是备份日志成功，但是日志文件没有释放掉~

Answer 5

手动执行checkpoint呢

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 6

都能成功，但是就是日志文件的空间释放不了

Answer 7

执行一下这个，把返回结果给我们

-- Do we have any reason why the log can't be purged?
	SELECT log_reuse_wait_desc,* 
	FROM sys.databases
	WHERE name = 'InMemoryOLTP'
	GO
	-- What percentage of the log is used for this DB?
	DBCC sqlperf(logspace)
	GO
	-- How much log is generated since last checkpoint?
	SELECT log_bytes_since_last_close /(1024*1024) as Log_bytes_since_last_close_mb,time_since_last_close_in_ms, current_checkpoint_id,* 
	FROM sys.dm_db_xtp_checkpoint_stats
	GO
	-- Have checkpoint files been flushed?
	SELECT  state_desc, 
       file_type_desc, 
       count(state_desc) count, 
       sum(file_size_in_bytes)/(1024*1024) file_size_in_mb_bytes,
	   Avg(file_size_in_bytes)/(1024*1024) Avgfile_size_in_mb_bytes
	FROM sys.dm_db_xtp_checkpoint_files
	GROUP BY state_desc, file_type_desc
	ORDER BY file_size_in_mb_bytes desc

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 8

log_reuse_wait_desc                                          name                                                                                                                             database_id source_database_id owner_sid                                                                                                                                                                    create_date             compatibility_level collation_name                                                                                                                   user_access user_access_desc                                             is_read_only is_auto_close_on is_auto_shrink_on state state_desc                                                   is_in_standby is_cleanly_shutdown is_supplemental_logging_enabled snapshot_isolation_state snapshot_isolation_state_desc                                is_read_committed_snapshot_on recovery_model recovery_model_desc                                          page_verify_option page_verify_option_desc                                      is_auto_create_stats_on is_auto_create_stats_incremental_on is_auto_update_stats_on is_auto_update_stats_async_on is_ansi_null_default_on is_ansi_nulls_on is_ansi_padding_on is_ansi_warnings_on is_arithabort_on is_concat_null_yields_null_on is_numeric_roundabort_on is_quoted_identifier_on is_recursive_triggers_on is_cursor_close_on_commit_on is_local_cursor_default is_fulltext_enabled is_trustworthy_on is_db_chaining_on is_parameterization_forced is_master_key_encrypted_by_server is_query_store_on is_published is_subscribed is_merge_published is_distributor is_sync_with_backup service_broker_guid                  is_broker_enabled log_reuse_wait log_reuse_wait_desc                                          is_date_correlation_on is_cdc_enabled is_encrypted is_honor_broker_priority_on replica_id                           group_database_id                    resource_pool_id default_language_lcid default_language_name                                                                                                            default_fulltext_language_lcid default_fulltext_language_name                                                                                                   is_nested_triggers_on is_transform_noise_words_on two_digit_year_cutoff containment containment_desc                                             target_recovery_time_in_seconds delayed_durability delayed_durability_desc                                      is_memory_optimized_elevate_to_snapshot_on
------------------------------------------------------------ -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ----------------------- ------------------- -------------------------------------------------------------------------------------------------------------------------------- ----------- ------------------------------------------------------------ ------------ ---------------- ----------------- ----- ------------------------------------------------------------ ------------- ------------------- ------------------------------- ------------------------ ------------------------------------------------------------ ----------------------------- -------------- ------------------------------------------------------------ ------------------ ------------------------------------------------------------ ----------------------- ----------------------------------- ----------------------- ----------------------------- ----------------------- ---------------- ------------------ ------------------- ---------------- ----------------------------- ------------------------ ----------------------- ------------------------ ---------------------------- ----------------------- ------------------- ----------------- ----------------- -------------------------- --------------------------------- ----------------- ------------ ------------- ------------------ -------------- ------------------- ------------------------------------ ----------------- -------------- ------------------------------------------------------------ ---------------------- -------------- ------------ --------------------------- ------------------------------------ ------------------------------------ ---------------- --------------------- -------------------------------------------------------------------------------------------------------------------------------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------- --------------------- --------------------------- --------------------- ----------- ------------------------------------------------------------ ------------------------------- ------------------ ------------------------------------------------------------ ------------------------------------------

(0 行受影响)

Database Name                                                                                                                    Log Size (MB) Log Space Used (%) Status
-------------------------------------------------------------------------------------------------------------------------------- ------------- ------------------ -----------
master                                                                                                                           2.492188      30.83856           0
tempdb                                                                                                                           22867.37      25.51944           0
model                                                                                                                            4.117188      98.23293           0
msdb                                                                                                                             7.429688      47.64064           0
KWF                                                                                                                              379.4297      99.8309            0
UMS                                                                                                                              98958.09      99.62591           0
MembershipCenter2.0                                                                                                              1513.68       97.29972           0
BackDB                                                                                                                           7405.93       33.49099           0

(8 行受影响)

DBCC 执行完毕。如果 DBCC 输出了错误信息，请与系统管理员联系。
Log_bytes_since_last_close_mb log_generated_since_last_checkpoint_in_bytes current_lsn                             log_to_process_in_bytes total_log_blocks_processed total_log_records_processed xtp_log_records_processed total_wait_time_in_ms waits_for_io         io_wait_time_in_ms   waits_for_new_log    new_log_wait_time_in_ms log_generated_since_last_checkpoint_in_bytes time_since_last_checkpoint_in_ms checkpoint_lsn                          current_lsn                             end_of_log_lsn                          task_address
----------------------------- -------------------------------------------- --------------------------------------- ----------------------- -------------------------- --------------------------- ------------------------- --------------------- -------------------- -------------------- -------------------- ----------------------- -------------------------------------------- -------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ------------------
0                             0                                            0                                       0                       0                          0                           0                         0                     0                    0                    0                    0                       0                                            0                                0                                       0                                       0                                       NULL

(1 行受影响)

state_desc                                                   file_type_desc                                               count       file_size_in_mb_bytes Avgfile_size_in_mb_bytes
------------------------------------------------------------ ------------------------------------------------------------ ----------- --------------------- ------------------------
PRECREATED                                                   DATA                                                         2           256                   128
PRECREATED                                                   DELTA                                                        2           16                    8
MERGED SOURCE                                                DATA                                                         3           0                     0
MERGED SOURCE                                                DELTA                                                        3           0                     0
TOMBSTONE                                                    NULL                                                         84          NULL                  NULL
REQUIRED FOR BACKUP/HA                                       DELTA                                                        32          NULL                  NULL
REQUIRED FOR BACKUP/HA                                       DATA                                                         32          NULL                  NULL
警告: 聚合或其他 SET 操作消除了 Null 值。

(7 行受影响)

Answer 9

版主，能否加下我QQ：382447403，劳烦了，呜呜呜~~

Answer 10

请参考这篇博客：https://www.mssqltips.com/sqlservertip/5029/monitoring-free-space-for-sql-server-inmemory-checkpoint-files/的Measure Checkpoint File Space Usage部分，并将结果反馈给我们。

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 11

之前在2016、2017上用内存表，结果重启和AlwaysOn都经常卡住

后来在补丁打上后，重启是解决了，但AlwaysOn依然经常卡住

而且不使用内存表了，其文件流文件删除不掉，真是问题一大堆

---

SQL Server 2016 ~ 2000 性能优化、方案设计 QQ:315054403 田园嘉兴