Sunday, 27 May 2012

Backup on SQL Server 2008 R2 database fails if you enable change tracking on database

BACKUP DATABASE [DB_Name] TO  DISK = N'D:\MSSQL\DB_Name.bak'
WITH STATS = 10
GO


Executing the query "BACKUP DATABASE [DB_Name] TO  DISK = N'D:\MSSQL\DB_Name.bak' failed with the following error: "Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'.
Failed to flush the commit table to disk in dbid 5 due to error 2601. Check the errorlog for more information.
BACKUP DATABASE is terminating abnormally.
The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.


This error occuring due to change tracking enabled on the database and Microsoft confirmed this a bug in SQL 2008 R2 RTM version. Need to apply latest service pack to fix this issue.

Ref - http://support.microsoft.com/kb/2522893 , http://support.microsoft.com/kb/2603910

No comments:

Post a Comment