Duplicate key rows from the sys.syscommittab table in SQL Server

When you compare the in-memory SYSCOMMITTABLE and the on-disk sys.syscommittab file in Microsoft SQL Server, you may see duplicate key rows. These duplicate values may cause backup and checkpoint operations to fail.

This problem occurs because of a known issue in SQL Server change tracking.
To resolve the factors that cause the duplicate keys, apply one of the following fixes, as appropriate for your situation: 

FIX: A backup operation on a SQL Server 2008 or SQL Server 2008 R2 database fails if you enable change tracking on this database

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

FIX: Backup operation fails in a SQL Server 2008, SQL Server 2008 R2, or SQL Server 2012 database after you enable change tracking

Although these fixes prevent duplicate key rows from continuing to appear, they do not automatically remove the duplicate rows. Without removing the duplicate rows, the affected database cannot complete database checkpoints, and backups may fail. 

To remove the duplicate rows, use one of the following methods.

Method 1: Disable and enable change tracking

  1. Disable change tracking on the affected tables and database.
  2. Issue a manual database CHECKPOINT.
  3. Enable change tracking on the affected database and tables.

For more information about change tracking, see Enable and disable change tracking.

For information about how to issue a manual CHECKPOINT, see CHECKPOINT (Transact-SQL)

Method 2: Manually delete the duplicate rows

  1. Copy the Transact-SQL script at the end of the "Resolution" section into a text editor.
  2. Locate the <AFFECTED_DB> placeholder in the script, and replace it with the name of the affected database.
  3. Save the modified script to your hard disk as a .sql file. For example:
If you're running SQL Server 2014, you must grant the per-Service SID full control to the mssqlsystemresource.ldf and mssqlsystemresource.mdf files. To do this, follow these steps:
  1. Navigate to the Binn directory that corresponds to your Instance ID. For example:

    C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn
  2. Open the properties for mssqlsystemresource.ldf and mssqlsystemresource.mdf, and then click the Security tab.
  3. Locate the SQL Server service per-Service SID, and note the default permissions:

    *Read & execute
  4. Grant the SQL Server service per-Service SID Full Control, and then close the permissions dialog boxes.
  5. Start SQL Server in Single-User mode. For more information, see Start SQL Server in Single-User mode.
  6. Use a sqlcmd command line to connect to SQL Server under the Dedicated Administrator Connection (DAC). For example:

    sqlcmd -S PRODSERV1\MSSQLSERVER -A -E -i c:\temp\remove_duplicates.sql
    Then, execute the modified Transact-SQL script.
  7. Start SQL Server in Multi-User mode, and then verify that backup and CHECKPOINT operations against the affected database complete successfully. If step 4 was used, revert the permissions to the default values.

Transact-SQL script

--Create a temporary database to store the necessary rows required to remove the duplicate dataif exists(select 1 from sys.databases where name = 'dbChangeTrackingMetadata')begindrop database dbChangeTrackingMetadataendgocreate database dbChangeTrackingMetadatago --Table to store the contents of the SYSCOMMITTABLEuse dbChangeTrackingMetadatagocreate table dbo.t_SYSCOMMITTABLE (commit_ts bigint,xdes_id bigint,commit_lbn bigint,commit_csn bigint,commit_time datetime)go --Table to store the duplicate rows to be removed from the sys.syscommittab tablecreate table dbo.t_syscommittab (commit_ts bigint,xdes_id bigint,commit_lbn bigint,commit_csn bigint,commit_time datetime,dbfragid int)go --Enable the usage of OPENROWSETexec sys.sp_setbuildresource 1go --Change <AFFECTED_DB> to the database that contains the duplicate valuesUSE <AFFECTED DB>godeclare @rowcount bigintSET @rowcount = 0 --Copy all rows from the SYSCOMMITTABLE into the temporary databaseinsert into dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLESELECT commit_ts, xdes_id, commit_lbn, commit_csn, commit_timeFROM OpenRowset (table SYSCOMMITTABLE, db_id (), 0, 0) --Save the duplicate values into the temporary databaseinsert into dbChangeTrackingMetadata.dbo.t_syscommittabselect ondisk_ct.* from sys.syscommittab as ondisk_ctjoin dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE as inmem_cton ondisk_ct.xdes_id = inmem_ct.xdes_id --Delete the duplicate valuesdelete from sys.syscommittabwhere xdes_id in ( select xdes_id from dbChangeTrackingMetadata.dbo.t_syscommittab )set @rowcount = @@rowcountif (@rowcount > 0)beginprint ''print 'DELETED '+CAST(@rowcount as NVARCHAR(10))+' rows from sys.syscommittab that were also stored in SYSCOMMITTABLE'print ''endelsebeginprint ''print 'Failed to DELETE DUP rows from sys.syscommittab'print ''endexec sys.sp_setbuildresource 0go


Article ID: 3083381 - Last Review: 09/25/2015 15:27:00 - Revision: 4.0

Microsoft SQL Server 2008 Standard, Microsoft SQL Server 2008 Developer, Microsoft SQL Server 2008 Enterprise, Microsoft SQL Server 2008 Workgroup, Microsoft SQL Server 2008 R2 Standard, Microsoft SQL Server 2008 R2 Developer, Microsoft SQL Server 2008 R2 Enterprise, Microsoft SQL Server 2008 R2 Workgroup, Microsoft SQL Server 2012 Standard, Microsoft SQL Server 2012 Developer, Microsoft SQL Server 2012 Enterprise, Microsoft SQL Server 2012 Web, Microsoft SQL Server 2014 Standard, Microsoft SQL Server 2014 Developer, Microsoft SQL Server 2014 Enterprise, Microsoft SQL Server 2014 Web

  • KB3083381