trace flag 1448 / AG Async 節點異常導致CDC功能停滯

2024-05-23 置頂文章

在secondary上 Suspend data movement 以模擬Async failed

--On Primary

insert into tb1 values(7,'a')

DECLARE @begin_time datetime, @end_time datetime, @from_lsn binary(10), @to_lsn binary(10);

-- Obtain the beginning of the time interval.

SET @begin_time = DATEADD(day, -1, GETDATE()) ;

-- Obtain the end of the time interval.

SET @end_time = GETDATE();

-- Map the time interval to a change data capture query range.

SET @from_lsn = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time);

SET @from_lsn = ISNULL(sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @begin_time), [sys].[fn_cdc_get_min_lsn]('dbo_tb1') );

SET @to_lsn = sys.fn_cdc_map_time_to_lsn('largest less than or equal', @end_time);

-- Return the net changes occurring within the query window.

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_tb1(@from_lsn, @to_lsn, 'all');

select * from sys.dm_cdc_errors -- 沒有錯誤

;WITH JobDetails

AS (

SELECT DISTINCT Job_Id = left(intr1, charindex(':', intr1) - 1)

,Step = substring(intr1, charindex(':', intr1) + 1, charindex(')', intr1) - charindex(':', intr1) - 1)

,SessionId = spid

FROM master.dbo.sysprocesses x

CROSS APPLY (

SELECT replace(x.program_name, 'SQLAgent - TSQL JobStep (Job ', '')

) cs(intr1)

WHERE spid > 50

AND x.program_name LIKE 'SQLAgent - TSQL JobStep (Job %'

)

SELECT SessionId, name,*

FROM msdb.dbo.sysjobs j

INNER JOIN JobDetails jd ON jd.Job_Id = convert(VARCHAR(max), convert(BINARY (16), j.job_id), 1)

select * from sys.sysprocesses where spid = 56

dbcc inputbuffer(56)

-- enable 1448 globally, cdc / replication 就正常了

DBCC TRACEON (1448,-1);