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.
Best Answer
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.
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.