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