March 23, 2015 at 9:56 am
I have located a bug in the functions cdc.fn_cdc_get_net_changes_<capture_instance> generated when you enable cdc on a table. This bug can be triggered if 2 rows are created in the _CT table having the same values for the __$start_lsn, __$seqval and the table's key column(s). From research on the internet I have found such rows can be created by a "deferred update": a single update statement in which a column that is part of a unique constraint is updated.
In order to report the bug with Microsoft I need to create a complete series of steps-to-reproduce. But even though the situation happens several times a day in our production environment, I have not yet been able to reproduce it in my test environment. Some help is very welcome therefore.
I need a single update statement (plus maybe some steps in advance) that make that the log reader inserts 2 rows into the _CT table, one with __$operation = 1 (delete) and another with __$operation = 2 (insert) as opposed to the single row with __$operation = 4 that it inserts for a normal update. Below is the script I have so far to create a fresh database, enable cdc, create a test table, insert some data and update this data.
I would have liked the last update statement to be handled as a "deferred update". However in all of my tests the log reader just simply inserts a single row into the cdc.dbo_NETTEST_CT table. Please help me to reproduce the situation where I get the 2 rows with __$operation 1 and 2 from a single update statement instead of the single row with __$operation = 4.
CREATE DATABASE [cdcnet]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'cdcnet', FILENAME = N'S:\SQLDATA\cdcnet.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'cdcnet_log', FILENAME = N'T:\SQLLOG\cdcnet_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [cdcnet] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [cdcnet] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [cdcnet] SET ANSI_NULLS OFF
GO
ALTER DATABASE [cdcnet] SET ANSI_PADDING OFF
GO
ALTER DATABASE [cdcnet] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [cdcnet] SET ARITHABORT OFF
GO
ALTER DATABASE [cdcnet] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [cdcnet] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [cdcnet] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [cdcnet] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [cdcnet] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [cdcnet] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [cdcnet] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [cdcnet] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [cdcnet] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [cdcnet] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [cdcnet] SET DISABLE_BROKER
GO
ALTER DATABASE [cdcnet] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [cdcnet] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [cdcnet] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [cdcnet] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [cdcnet] SET READ_WRITE
GO
ALTER DATABASE [cdcnet] SET RECOVERY FULL
GO
ALTER DATABASE [cdcnet] SET MULTI_USER
GO
ALTER DATABASE [cdcnet] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [cdcnet] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [cdcnet]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [cdcnet] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
USE [cdcnet]
go
CREATE TABLE dbo.NETTEST (
ID INT NOT NULL,
A varchar(10) NULL,
CONSTRAINT PK_NETTEST PRIMARY KEY NONCLUSTERED (ID)
)
GO
CREATE UNIQUE CLUSTERED INDEX ixU1 on dbo.NETTEST(A) ;
GO
EXEC sys.sp_cdc_enable_db
GO
EXEC sys.sp_cdc_enable_table
@source_schema = N'dbo',
@source_name = N'NETTEST',
@role_name = 'CDCReaders',
@supports_net_changes = 1,
@filegroup_name = N'PRIMARY';
GO
--Job 'cdc.cdcnet_capture' started successfully.
--Job 'cdc.cdcnet_cleanup' started successfully.
select * from cdc.dbo_nettest_ct
go
insert dbo.NETTEST(ID, A)
values (1, 'test')
GO
select * from cdc.dbo_nettest_ct
go
UPDATE dbo.NETTEST
SET
ID = 1,
A = 'test 2'
WHERE ID = 1
GO
select * from cdc.dbo_nettest_ct
go
Tested on these SQL server versions:
Microsoft SQL Server 2012 (SP1) - 11.0.3460.0 (X64)
Jul 22 2014 15:22:00
Copyright (c) Microsoft Corporation
Enterprise Edition: Core-based Licensing (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Microsoft SQL Server 2012 (SP1) - 11.0.3349.0 (X64)
Mar 8 2013 17:33:56
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)
Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
Note: the bug I have located and found a fix for has been reported back in 2011 as Connect ID 690476 and has since been Closed with a nonsense workaround. The problem is however still in SQL Server 2012 and is a.o. breaking the feed into our production datawarehouse at random times. As soon as I have the steps to reproduce complete with your help I will open a new item, referring to this old one. For details on the bug itself please review the Connect item.
EDIT:
I've tried switching the key values on 2 rows, like such: UPDATE dbo.NETTEST
SET
ID = case ID when 1 then 10000 when 10000 then 1 end,
A = 'test 2'
WHERE ID in (1, 10000)
This does result in the 2 rows output I requested. However the __$seqval values on the both rows output for each key value are not the same. i.e. this is not the situation I am looking for.
April 26, 2015 at 7:00 am
The following example (using your cdcnet database setup) creates the delete/insert __$operation with the same __$seqval every time on my SQL Server 2012 Developer SP2 CU4 (11.0.5569) box. The __$update_mask is also useless for auditing purposes when this happens (since the entire mask is set for inserts and deletes), so it looks like we have to detect this scenario and reinvent the wheel to accurately record which columns were actually modified in our audit database.
We noticed it with a table with a unique index which was filtered, but the same issue occurs in the example below if the unique index is created without the filter. The issue will occur on columns listed in either the index column list or in the filter (if one exists).
CREATE DATABASE [cdcnet]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'cdcnet', FILENAME = N'E:\SQLDATA\cdcnet.mdf' , SIZE = 4096KB , FILEGROWTH = 1024KB )
LOG ON
( NAME = N'cdcnet_log', FILENAME = N'E:\SQLLOG\cdcnet_log.ldf' , SIZE = 1024KB , FILEGROWTH = 10%)
GO
ALTER DATABASE [cdcnet] SET COMPATIBILITY_LEVEL = 110
GO
ALTER DATABASE [cdcnet] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [cdcnet] SET ANSI_NULLS OFF
GO
ALTER DATABASE [cdcnet] SET ANSI_PADDING OFF
GO
ALTER DATABASE [cdcnet] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [cdcnet] SET ARITHABORT OFF
GO
ALTER DATABASE [cdcnet] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [cdcnet] SET AUTO_CREATE_STATISTICS ON
GO
ALTER DATABASE [cdcnet] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [cdcnet] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [cdcnet] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [cdcnet] SET CURSOR_DEFAULT GLOBAL
GO
ALTER DATABASE [cdcnet] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [cdcnet] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [cdcnet] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [cdcnet] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [cdcnet] SET DISABLE_BROKER
GO
ALTER DATABASE [cdcnet] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [cdcnet] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [cdcnet] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [cdcnet] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [cdcnet] SET READ_WRITE
GO
ALTER DATABASE [cdcnet] SET RECOVERY FULL
GO
ALTER DATABASE [cdcnet] SET MULTI_USER
GO
ALTER DATABASE [cdcnet] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [cdcnet] SET TARGET_RECOVERY_TIME = 0 SECONDS
GO
USE [cdcnet]
GO
IF NOT EXISTS (SELECT name FROM sys.filegroups WHERE is_default=1 AND name = N'PRIMARY') ALTER DATABASE [cdcnet] MODIFY FILEGROUP [PRIMARY] DEFAULT
GO
USE [cdcnet]
go
CREATE TABLE tABC (A INT NOT NULL PRIMARY KEY, B INT NOT NULL, C DATETIME NOT NULL)
CREATE UNIQUE INDEX UK_tABC_B ON tABC (B) WHERE C > '1/1/2014'
EXEC sys.sp_cdc_enable_table 'dbo', 'tABC', 'dbo_tABC', 0, 'RoleCDC'
-- Stop the agent capture job if it is running before executing the rest of this
INSERT tabc (A,B,C) VALUES (1, 1, GETDATE()), (2, 2, '1/1/2014')
EXEC sp_cdc_scan @continuous = 0
-- Can rerun this part over and over
DECLARE @lastLSN BINARY(10) = ISNULL((SELECT MAX(__$start_lsn) FROM cdc.dbo_tABC_CT), 0x)
UPDATE tABC SET C = GETDATE()
UPDATE tABC SET B += 2
EXEC sp_cdc_scan @continuous = 0
SELECT *
FROM cdc.dbo_tABC_CT
WHERE __$start_lsn > @lastLSN
ORDER BY __$start_lsn desc
May 4, 2015 at 7:14 am
Thank you dlcraig. I will have a look at your test script to see if it produces the same result for me. I'm having a holiday right now, but I will get back to you on monday 11th.
EDIT:
Hi dlcraigg. I've had a further look and a colleague of mine found that the behavior you've found is documented behavior of the logreader:https://support.microsoft.com/en-us/kb/238254/en-us.
Further experiments then lead me to the discovery that the cdc.fn_cdc_get_net_changes_<capture_instance> functions are now slightly differently generated than they previously were in our production environment. My server version is now at Microsoft SQL Server 2012 - 11.0.5058.0 (X64)
May 14 2014 18:34:29
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)
This version generates a new additional condition
and t.__$operation = (
select
max(mo.__$operation)
from
[cdc].[dbo_tABC_CT] as mo with (nolock)
where
mo.__$seqval = t.__$seqval
and
( (t.[A] = mo.[A]) )
group by
mo.__$seqval
)
in the fn_cdc_get_net_changes_ functions that isn't there in the versions generated earlier. This new condition successfully prevents the fn's output from containing duplicates in the case of the defered update. i.e. it seems MS have updated the generator for the fn_cdc_get_net_changes_ functions to fix the bug, but we have missed their note telling us to re-generate these functions, incluis the instructions how to do so....
I'll be looking into how to regenerate these functions without loosing the change data on Monday 11th. But maybe you've already solved your issue by then. If you do find how to regenerate the functions before I get back, I'm looking forward to some more feedback here. 😛 Thanks a lot for your help!
Richard Rozema
May 4, 2015 at 9:54 am
Oh and maybe to help you with your issues, here's a way to use cdc to only act upon changed data without using the mask feature: (I've typed this without being able to run it so it may contain some typos, but you should be able to get the idea)
with cteSource as (
select
fn.A,
fn.B,
fn.C
from cdc.fn_cdc_get_net_changes_dbo_tABC(@from_lsn, @to_lsn, 'all with merge') fn
),
cteTarget as (
select
t.A,
t.B,
t.C
from <Target_Table> t
where exists (
select *
from cdc.fn_cdc_get_all_changes_dbo_tABC(@from_lsn, @to_lsn, 'all update old') fn
where fn.__$operation = 5
and fn.A = t.A
)
)
merge into cteTarget t
using cteSource s
on (s.A = t.A)
when not matched by target
then
insert (A, B, C)
values(s.A, s.B, s.C)
when not matched by source
then
delete
when matched and (
isnull(nullif(s.B,t.B),nullif(t.B,s.B)) is not null
or isnull(nullif(s.C,t.C),nullif(t.C,s.C)) is not null
)
then
update
set
B = s.B,
C = s.C;
May 27, 2015 at 2:38 am
The extra where clause that MS added to fix the deferred update issue does functionally fix the issue on small(ish) numbers of rows in the _CT tables. When however I try to run it on a table with 500.000 rows in the _CT table, the merge query that used to run in +- 30 seconds, doesn't finish after having run for at least a day and a half... 🙁
I've created a script that generates my own version of the net changes functions as cdc.fn_get_net_changes_JFH_<capture instance>(). (JFH is my employer). I'm not completely done testing it functionally, and the generated functions only support 'all with merge', but this version both seems to fix the deferred-update issue and performs well, even with larger change sets. I've kept the functions compatible with MS' net_changes functions, so a global search and replace of the function names should be sufficient to switch from using one or the other in your sources. You're welcome to test it and maybe give comments?
declare cur cursor local forward_only
for
select
N'create function [cdc].' + quotename('fn_cdc_get_net_changes_JFH_' + ct.capture_instance )
+ crlf + N'(@from_lsn binary(10),'
+ crlf + N'@to_lsn binary(10),'
+ crlf + N'@row_filter_option nvarchar(30)'
+ crlf + N')'
+ crlf + N'returns table'
+ crlf + N'return'
+ crlf + N''
+ crlf + N' with cte as ('
+ crlf + N' select'
+ crlf + N' t1.[__$start_lsn],'
+ crlf + N' t1.[__$end_lsn],'
+ crlf + N' t1.[__$seqval],'
+ crlf + N' x.[__$operation]'
+ (
select N','
+ crlf + N' t1.' + quotename(col.column_name) as [text()]
from cdc.captured_columns col
where col.object_id = ct.object_id
-- and col.is_computed = 0
order by col.column_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ crlf + N' from [cdc].' + quotename(ct.capture_instance + '_CT') + ' t1 with (nolock)'
+ crlf + N' outer apply ('
+ crlf + N' select'
+ crlf + N' case'
+ crlf + N' when exists ('
+ crlf + N' select *'
+ crlf + N' from [cdc].' + quotename(ct.capture_instance + '_CT') + ' t2 with (nolock)'
+ crlf + N' where t2.[__$start_lsn] = t1.[__$start_lsn]'
+ crlf + N' and t2.[__$seqval] = t1.[__$seqval]'
+ crlf + N' and t2.[__$operation] = case t1.[__$operation] when 1 then 2 when 2 then 1 end'
+ (
select
crlf + N' and t2.' + quotename(ic.column_name) + ' = t1.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ crlf + N' )'
+ crlf + N' then'
+ crlf + N' case t1.[__$operation]'
+ crlf + N' when 1 then 3'
+ crlf + N' when 2 then 4'
+ crlf + N' end'
+ crlf + N' else'
+ crlf + N' t1.[__$operation]'
+ crlf + N' end as [__$operation]'
+ crlf + N' where (t1.[__$operation] = 1 or t1.[__$operation] = 2)'
+ crlf + N''
+ crlf + N' union all'
+ crlf + N''
+ crlf + N' select t1.[__$operation]'
+ crlf + N' where (t1.[__$operation] = 3 or t1.[__$operation] = 4)'
+ crlf + N' ) x'
+ crlf + N' )'
+ crlf + N''
+ crlf + N'select'
+ crlf + N' NULL as __$start_lsn,'
+ crlf + N' NULL as __$operation,'
+ crlf + N' NULL as __$update_mask'
+ (
select N','
+ crlf + N' NULL as ' + quotename(col.column_name) as [text()]
from cdc.captured_columns col
where col.object_id = ct.object_id
-- and col.is_computed = 0
order by col.column_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ crlf + N'where ( [sys].[fn_cdc_check_parameters]( N' + quotename(ct.capture_instance, '''') + ', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 0)'
+ crlf + N''
+ crlf + N'union all'
+ crlf + N''
+ crlf + N' select'
+ crlf + N' tbl.__$start_lsn,'
+ crlf + N' case tbl.__$operation'
+ crlf + N' when 1 then 1'
+ crlf + N' else 5'
+ crlf + N' end as __$operation,'
+ crlf + N' null as __$update_mask'
+ (
select N','
+ crlf + N' tbl.' + quotename(col.column_name) as [text()]
from cdc.captured_columns col
where col.object_id = ct.object_id
-- and col.is_computed = 0
order by col.column_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ crlf + N' from cte tbl with (nolock)'
+ crlf + N' inner join ('
+ crlf + N' select'
+ (
select
crlf + N' ct.' + quotename(ic.column_name) + ',' as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)')
+ crlf + N' min( case ct.__$operation when 2 then ct.__$seqval end) as insert_min_seqval,'
+ crlf + N' max( ct.__$seqval) as max_seqval,'
+ crlf + N' min( ct.__$seqval) as min_seqval'
+ crlf + N' from cte ct with (nolock)'
+ crlf + N' where lower(rtrim(ltrim(@row_filter_option))) = N''all with merge'''
+ crlf + N' and ( [sys].[fn_cdc_check_parameters]( N' + quotename(ct.capture_instance, '''') + ', @from_lsn, @to_lsn, lower(rtrim(ltrim(@row_filter_option))), 1) = 1)'
+ crlf + N' and ct.__$start_lsn >= @from_lsn'
+ crlf + N' and ct.__$start_lsn <= @to_lsn'
+ crlf + N' and (ct.__$operation = 1 or ct.__$operation = 2 or ct.__$operation = 4)'
+ crlf + N' group by'
+ stuff((
select N','
+ crlf + N' ct.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 1, '')
+ crlf + N' ) t on ('
+ crlf + N' '
+ stuff((
select
crlf + N' and t.' + quotename(ic.column_name) + N' = tbl.' + quotename(ic.column_name) as [text()]
from cdc.index_columns ic
where ic.object_id = ct.object_id
order by ic.index_ordinal
for xml path(''), type
).value('.','nvarchar(max)'), 1, 15, '')
+ crlf + N' and t.max_seqval = tbl.__$seqval'
+ crlf + N' and tbl.__$start_lsn >= @from_lsn'
+ crlf + N' and tbl.__$start_lsn <= @to_lsn'
+ crlf + N' and (tbl.__$operation = 2 -- Insert,'
+ crlf + N' or tbl.__$operation = 4 -- Update or'
+ crlf + N' or (tbl.__$operation = 1 -- Delete ...'
+ crlf + N' and (t.insert_min_seqval is null or t.insert_min_seqval <> t.min_seqval) -- ... and existed before this batch already.'
+ crlf + N' )))'
from (
select char(0x0d) + char(0x0a)
) s (crlf)
cross join cdc.change_tables ct;
open cur;
while 1 = 1
begin
declare @stmt nvarchar(max);
fetch next from cur into @stmt;
if @@fetch_status = -1
break;
if @@fetch_status = 0
begin
exec sp_executesql @stmt;
end
end
close cur;
deallocate cur;
edit: Changed code to use cdc.captured_columns and cdc.index_columns instead of sys.columns and sys.indexes & sys.index_columns.
March 23, 2016 at 10:33 am
I am having a similar issue. However, not sure about the fix. I am getting an error message which looks like this:
Cannot insert duplicate key row in object 'xxxxxxxxx_CT' with unique index 'xxxxxxxxxxxxxxxx_idx'. The duplicate key value is (0x00089394000251b400fa, 0x000893930003f00300d1, 1).
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {00089394:000251b4:00f9}. Back up the publication database and contact Customer Support Services.
Could you suggest something about this. I have validated that in our case, we do see deferred updates however, I am not able to understand why constraint failures on the unique clustered index of the capture table itself.
April 8, 2016 at 8:49 am
jaincs (3/23/2016)
I am having a similar issue. However, not sure about the fix. I am getting an error message which looks like this:Cannot insert duplicate key row in object 'xxxxxxxxx_CT' with unique index 'xxxxxxxxxxxxxxxx_idx'. The duplicate key value is (0x00089394000251b400fa, 0x000893930003f00300d1, 1).
The Log-Scan Process failed to construct a replicated command from log sequence number (LSN) {00089394:000251b4:00f9}. Back up the publication database and contact Customer Support Services.
Could you suggest something about this. I have validated that in our case, we do see deferred updates however, I am not able to understand why constraint failures on the unique clustered index of the capture table itself.
That does not look like my issue at all. In your case the log reader can't write it's output into the change tables. Whereas in my case the log reader did successfully write its output, but the contents of the change tables can't be interpreted correctly. I suggest you create a new topic asking for help with your problems.
April 25, 2016 at 3:03 pm
I have a similar issue. CDC captured failed, Cannot insert duplicate key row in object xxx_CT with unique index. Do you find any solution for it?
We got this issue just after installed SQL 2012 SP3 last week.
Any suggestions to help me out?
April 25, 2016 at 3:40 pm
Yeah, indeed. This is different. I had thought the underlying cause could be "deferred updates" but I was wrong. In my case, I discovered that this is a bug with help from Microsoft PSS.
September 27, 2016 at 10:54 am
Hi,
Did you find what is causing this issue ? I am experiencing the same problem on a live server.
It happens randomly in different tables where cdc is enabled and to fix it we have to disable and re-enable cdc on the table.
Jonathan Bernardez Bernardez
___________________________________________________________
DBD. MCSA SQL Server 2012
September 30, 2016 at 4:10 pm
which issue is occuring. the deferred updates or the unique constraint violation.
In my case it was unique constraint violation on CT table though. the CDC capture job was having issue with handling the deferred update scenario. I had opened a case with MS and they recommended applying a hotfix to resolve the issue. However, in my case I was able to avoid the situation by making some code changes in MERGE statement to avoid updating the UNIQUE constraint on the table thereby avoiding deferred updates.
We had a PK on identity column and unique constraint on one table that was in CDC. when the MERGE ran and was updating the unique key columns (even though the value did not really change) caused deferred updates. We changed the code to avoid update on the unique key columns. Since then no issues are observed.
I was recommended by MS support to update SQL server to following version:
SQL Server 2012 SP3 CU3 (11.0.6531.5)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply