SQL Server 2014 sp2问题与tempdb和同步（可能相关） [英] SQL Server 2014 sp2 problem with tempdb and synchronization (maybe related)

问题描述

https://support.microsoft.com/en-gb/help/3173471/fix-could-not-redo-log-record- error-and-replica-is-suspended-in-sql-se

https://support.microsoft.com/en-gb/help/3173471/fix-could-not-redo-log-record-error-and-replica-is-suspended-in-sql-se

我遇到与上述相同的问题，但是我的问题服务器是SQL Server 2014 sp2 ... 

推荐答案

您好HBShare，

Hi HBShare,

 

>>我的tempDB非常庞大，有时日志会变满活动交易

>>my tempDB is very huge and sometimes the log becomes full due to active transaction

 

请执行 以下c q的命令 uery if ther e是一个大型查询计划
或 临时表 ：

-- Obtaining the space consumed by internal objects in all currently running tasks in eachsession 
SELECT session_id,
SUM(internal_objects_alloc_page_count) AS
task_internal_objects_alloc_page_count,
SUM(internal_objects_dealloc_page_count) AS
task_internal_objects_dealloc_page_count FROM sys.dm_db_task_space_usage GROUP
BY session_id; 

-- Obtaining the space consumed by internal objects in the current session for both running and completed tasks 
SELECT
R2.session_id, R1.internal_objects_alloc_page_count +
SUM(R2.internal_objects_alloc_page_count) AS
session_internal_objects_alloc_page_count,
R1.internal_objects_dealloc_page_count +
SUM(R2.internal_objects_dealloc_page_count) AS session_internal_objects_dealloc_page_count
FROM sys.dm_db_session_space_usage AS R1 INNER JOIN sys.dm_db_task_space_usage
AS R2 ON R1.session_id = R2.session_id GROUP BY R2.session_id,
R1.internal_objects_alloc_page_count, R1.internal_objects_dealloc_page_count;;

 

通过Always On Availability Group Dashboard查看重做队列大小 ：

 

 

最好的问候，

Dedmon Dai

Dedmon Dai

这篇关于SQL Server 2014 sp2问题与tempdb和同步（可能相关）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！