Error 2601, Severity 14, State 1, Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'

Hi Team,

Here by I would like to share a problem which I faced in once of my account,

Environment
****************

SQL Server 2008 Enterprise Edition x64 10.00.2531 (Service Pack 1)
Windows 2003 Enterprise Edition x64 SP2

Problem Description
*************************

* We have CHANGE TRACKING enabled on one of our database.
* Because of some problem we restarted SQL Server Services and after that we start getting below error messages:

2010-03-30 09:47:57.42 spid13s Error: 2601, Severity: 14, State: 1.
2010-03-30 09:47:57.42 spid13s Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'.
2010-03-30 09:47:57.42 spid13s Error: 3999, Severity: 17, State: 1.
2010-03-30 09:47:57.42 spid13s Failed to flush the commit table to disk in dbid 6 due to error 2601. Check the errorlog for more information.

* Manual CHECKPOINT operation & Backup of database also got failed with the same error.

Resolution As Per KB Article
*****************************

* Microsoft Identified this problem as a bug and as per resolution one need to apply SQL Server 2008 Service Pack 1 Cummulative Update 7.
* Below is the KB artcile.

A backup Operation On A SQL Server 2008 Database Fails If You Enable Change Tracking On This Database
http://support.microsoft.com/kb/978839

Twist In The Tail
*****************

* This KB has a WORK AROUND section which talk about:

Restarting SQL Server Services
Disable Change Tracking
Re-enable Change Tracking

* We did worked as per this method (below) but it didn't resolved our problem :

Restart SQL Server Services.
Disable Change Tracking On All The Tables Involved.
Disable Change Tracking at Database Level.
Re-enabled Change Tracking at Database Level,
Re-enabled Change Tracking on All the Tables Involved.

* We applied Cummulative Update 7 of Service Pack 1 of SQL Server 2008 but still problem persists.

* The reason was that still somehow some system was trying to insert Duplicate Values in table "SYS.SYSCOMMITTAB".

* We checked with Microsoft and came to know that we have to first delete DUPLICATE rows from this "SYS.SYSCOMMITTAB"
table.

* We put Database into SINGLE user mode.

* We started DAC Connection (As we have to modify SYSTEM Table).

* After connecting to DAC we ran below query to identify DUPLICATE Rows and then DELETE it.
In this below script we first created a new database and then moved all rows into this newly
created database and then deleted duplicate rows from SOURCE database.


-- Script to extract contents of SYSCOMMITTABLE
-- SYSCOMMITTABLE is the in-memory buffer that stores syscommittab rows before they are
-- flushed to sys.syscommittab (on disk)
--
-- Script must be run with server in single user mode and from a DAC connection:
-- sqlcmd -S -A -i c:\debuggingtools\ChangeTracking\Extract_SYSCOMMITTABLE_contents.sql

if exists(select 1 from sys.databases where name = 'dbChangeTrackingMetadata')
begin
drop database dbChangeTrackingMetadata
end
go

create database dbChangeTrackingMetadata
go

use dbChangeTrackingMetadata
go

-- Will store contents of SYSCOMMITTABLE
--

create table dbo.t_SYSCOMMITTABLE (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
)
go

-- Will store a backup of DUP rows to be removed from sys.syscommittab
--

create table dbo.t_syscommittab (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
,dbfragid int
)
go

-- Enables the below OpenRowset query against SYSCOMMITTABLE to work
--

exec sys.sp_setbuildresource 1
go


-- Here Database Name should be the the one in which we have enabled CHANGE TRACKING and
we are facing issue.

USE
go

declare @rowcount bigint
SET @rowcount = 0

-- Copy contents of SYSCOMMITTABLE
--

insert into dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE
SELECT commit_ts, xdes_id, commit_lbn, commit_csn, commit_time
FROM OpenRowset (table SYSCOMMITTABLE, db_id (), 0, 0)

-- Backup the rows to be deleted
--

insert into dbChangeTrackingMetadata.dbo.t_syscommittab
select ondisk_ct.* from sys.syscommittab as ondisk_ct
join dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE as inmem_ct
on ondisk_ct.xdes_id = inmem_ct.xdes_id

delete from sys.syscommittab
where xdes_id in ( select xdes_id from dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE )

set @rowcount = @@rowcount

if (@rowcount > 0)
begin
print ''
print 'DELETED '+CAST(@rowcount as NVARCHAR(10))+' rows from sys.syscommittab that were also stored in SYSCOMMITTABLE'
print ''
end
else
begin
print ''
print 'Failed to DELETE DUP rows from sys.syscommittab'
print ''
end

exec sys.sp_setbuildresource 0
go

* Once we deleted duplicate rows with above script after that we ran CHECKPOINT on this user database
and it completes succesfully.

* We started SQL Server in NORMAL Mode (Without putting in SINGLE user mode).

* We didn't got any further errors with regard to CHECKPOINT or Cannot Insert Duplicate Row in SYS.SYSCOMMITTAB table.

* We succesfully took bcakup of that user database.

Conclustion
************

* If we landed into such problem where we encounter above mentioned error messages while taking backup
then we have to first delete duplicate rows from this SYSTEM table and then apply CU7 of SP1 of SQL Server 2008.

* Before doing this deletion make sure that APPLICATION Team process the CHANGE RECORDS as there are chances
that while doing DELETION it can remove data which is not yet processed by the APPLICATION.

Regards
Gursethi