Skip to main content
edited tags
Link
Paul White
  • 90.3k
  • 30
  • 424
  • 663
Source Link

MS SQL Server 2008 R2 duplicate key row in object 'sys.syscommittab'

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