SQL Server 2008 unable to backup due to Failed to flush the commit table to disk in dbid 11 due to error 2601

Question

I tried to backup the SQL Server 2008 database using the following code:

BACKUP DATABASE [DataStore] TO  DISK ='E:\SQLServerDatabase\DataBasebackUpOct302012.bak'

and I get the following error:

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'.

Msg 3013, Level 16, State 1, Line 1 BACKUP DATABASE is terminating abnormally. The statement has been terminated.

Msg 3999, Level 17, State 1, Line 1 Failed to flush the commit table to disk in dbid 11 due to error 2601. Check the errorlog for more information.

Could somebody suggest a solution to this problem?

The version is Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (X64) Jul 9 2008 14:17:44 (64-bit) on Windows NT 6.1 (Build 7600: )

---

In reply to the comments below:

Aaron Bertrand, Thank you. I am going to look into service packs and hope that is going to resolve the issue.

SQL-Learner,

Never used the change tracking services before.But we had unexpected Windows Server shutdowns due to mishandling 2-3 times.

Looks like I have to apply a service pack.

mrdenny,

Results after using DBCC CHECKDB Errors:

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 statement has been terminated.
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'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 11 due to error 2601. Check the errorlog for more information.
Msg 3999, Level 17, State 1, Line 1
Failed to flush the commit table to disk in dbid 11 due to error 2601. Check the errorlog for more information

Update 11/9/2012 I applied the Service Pack3 to SQL Server 2008 but I still could not create the back of the database. I have not rebooted the server as it didn't ask for it. Will have to reboot and check if the patch applied.

Would appreciate any comments/ideas as to how to go about it. Thanks.

Answer 1

Do you happen to use the change tracking services provided by in SQL 2008 Enterprise? If so, it's time to update your system. Please try to avoid running RTM anywhere. It's very risky from a security and sometimes stability point of view. :)

The following Microsoft Knowledge Base articles may be of use to you:

---

FIX: Backup fails in SQL Server 2008, in SQL Server 2008 R2 or in SQL Server 2012 if you enable change tracking on the database

This fix was first released in Cumulative Update 7 for SQL Server 2008 Service Pack 2.

Symptoms:

Error: 2601, Severity: 14, State: 1. Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'Name'. Error: 3999, Severity: 17, State: 1. Failed to flush the commit table to disk in dbid due to error 2601.

---

FIX: The sys.syscommittab system table is not cleaned up after the retention period for change tracking in SQL Server 2008

This fix was first released in Cumulative Update 11 for SQL Server 2008 Service Pack 1.

Symptoms:

You enable the Change Tracking and Auto Cleanup properties for a database in Microsoft SQL Server 2008.

You set the Retention Period and Retention Period Units properties.
In this scenario, the sys.syscommittab system table for change tracking is not cleaned up after the specified retention period.