SQL Server 2008 R2 – Duplicate Key Row in sys.syscommittab

change-trackingsql serversql-server-2008-r2

I have a server that is failing to backup due to an error with the syscommittab and have gone through everything in this question: SQL Server 2008 unable to backup due to Failed to flush the commit table to disk in dbid 11 due to error 2601 but am still having the same issue

Running a DBCC CHECKDB returns the following:

Msg 2601, Level 14, State 1, Procedure sp_flush_commit_table, Line 15
    Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (491219680).
Msg 3999, Level 17, State 1, Line 1
    Failed to flush the commit table to disk in dbid 9 due to error 2601. Check the errorlog for more information.

Change tracking is enabled on this database with retention of 30 days and auto-cleanup on but it is not cleaning any records since this error occurred several months ago

I've seen suggestions to run sp_flush_commit_table_on_demand but that does not exist on this installation (SQL Server 2008 R2 SP3 – now, was SP1 but installed latest SP to address known issues with change tracking related to this error)

I can see the problem rows by running:

select * FROM sys.dm_tran_commit_table where xdes_id='491219680'

Both of them are from when the problem occurred (within 20 mins of each other) and are well outside of the retention period. I'm wary of trying to manually delete these but can't really find anything to confirm what the risks of doing this is

Any suggestions on next steps would be appreciated

Best Answer

Eventually I found a Microsoft article on how to do this. You can't simply delete the rows as they are in protected system tables so you need to put SQL into single user mode and run the script provided in the article from a DAC connection in order to delete the duplicate rows

https://support.microsoft.com/en-gb/help/3083381/duplicate-key-rows-from-the-sys-syscommittab-table-in-sql-server

Transact-SQL script from above article:

--Create a temporary database to store the necessary rows required to remove the duplicate data
if exists(select 1 from sys.databases where name = 'dbChangeTrackingMetadata')
begin
drop database dbChangeTrackingMetadata
end
go
create database dbChangeTrackingMetadata
go

--Table to store the contents of the SYSCOMMITTABLE
use dbChangeTrackingMetadata
go
create table dbo.t_SYSCOMMITTABLE (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
)
go

--Table to store the duplicate rows to be removed from the sys.syscommittab table
create table dbo.t_syscommittab (
commit_ts bigint
,xdes_id bigint
,commit_lbn bigint
,commit_csn bigint
,commit_time datetime
,dbfragid int
)
go

--Enable the usage of OPENROWSET
exec sys.sp_setbuildresource 1
go

--Change <AFFECTED_DB> to the database that contains the duplicate values
USE <AFFECTED DB>
go
declare @rowcount bigint
SET @rowcount = 0

--Copy all rows from the SYSCOMMITTABLE into the temporary database
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)

--Save the duplicate values into the temporary database
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 the duplicate values
delete from sys.syscommittab
where xdes_id in ( select xdes_id from dbChangeTrackingMetadata.dbo.t_syscommittab )
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
Related Question