SQL Server 2008 R2 Change Tracking and Backup Fix

My database server rebooted unexpectedly and I started getting this error each time I’d try to backup the database or do some other operations like dismount, etc.

Cannot insert duplicate key row in object 'sys.syscommittab' with unique index '<Index Name>'.

Failed to flush the commit table to disk in dbid <Database ID> due to error 2601.

Turns out that it is a known problem with SQL Server 2008 and 2008 R2, change tracking, and a database checkpoint.  Luckily  there is a fix, see KB 2522893.

In my case I had SQL Server 2008 R2 SP1 installed and had to request / apply cumulative update 2, latest one as of this writing.

Unfortunately the damage was already done to the database so I kept getting the error.  My options were to disable all change tracking on the tables then on the database or restore a backup from before the damage happened.  I tried both and each worked.  If you do a restore the database will retain the change tracking information though.

In my case I needed the change tracking information and my backup was missing some recent user data since the backup kept failing a few times before I could figure out what was wrong.  I ended up disabling change tracking on the damaged database, renamed it, restored my backup, and then imported the changed data from the damaged database into the restored database.

Anyhow it was uncomfortable recovery work so if you are using SQL Server 2008 change tracking make sure to get the cumulative update installed before this happens to you.