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

This article provides information about resolving a SQL Server Change Tracking issue that can result in duplicate rows in sys.syscommittab file.

Original product version:   SQL Server 2008 and the later versions
Original KB number:   3083381

Symptoms

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.

"Cannot insert duplicate key row in object 'sys.syscommittab' with unique index 'si_xdes_id'. The duplicate key value is (KeyValue).
Error: 3999, Severity: 17, State: 1.
Failed to flush the commit table to disk in dbidDatabaseID due to error 2601. Check the errorlog for more information."

Cause

This problem occurs because of a known issue in SQL Server change tracking.

Resolve factors that cause the duplicate keys

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 don't automatically remove the duplicate rows. Without removing the duplicate rows, the affected database can't complete database checkpoints, and backups may fail.

Disable and enable change tracking to remove duplicate rows

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 issuing a manual Checkpoint, see CHECKPOINT (Transact-SQL).

Manually delete the duplicate rows

1. Copy the Transact-SQL script from the Transact-SQL script 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, C:\temp\remove_duplicates.sql.

If you're using SQL Server 2014 and later versions, 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 Bin 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 select the Security tab.

3. Locate the SQL Server service per-Service SID, and note the default permissions:

   • *Read & execute
   • *Read

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.

   Note

   When SQL Server starts in single-user mode, it skips the startup process for databases that are part of an Always On availability group (AG). If you need to troubleshoot an issue with change tracking that requires starting SQL Server in single-user mode, and the database with change tracking enabled is also part of an AG, you must remove the database from the AG before starting SQL Server in single-user mode so that the database comes online.

6. Use a sqlcmd command line to connect to SQL Server under the Dedicated Administrator Connection (DAC) and execute the modified Transact-SQL script. For example:

   sqlcmd -S PRODSERV1\MSSQLSERVER -A -E -i c:\temp\remove_duplicates.sql
   

7. Restart 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 data 
USE master
GO
IF EXISTS(SELECT 1 FROM sys.databases WHERE name = 'dbChangeTrackingMetadata')
BEGIN
  DROP DATABASE dbChangeTrackingMetadata
END
GO
CREATE DATABASE dbChangeTrackingMetadata
GO

--Table to store the contents of the SYSCOMMITTABLE
USE dbChangeTrackingMetadata
GO
CREATE 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 table

CREATE 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 OPENROWSET
EXEC sys.sp_setbuildresource 1
GO

--Change <AFFECTED_DB> to the database that contains the duplicate values
USE <AFFECTED DB>
GO
DECLARE @rowcount BIGINT
SET @rowcount = 0

--Copy all rows from the SYSCOMMITTABLE INTo the temporary database
INSERT INTO dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE
SELECT commit_ts, xdes_id, commit_lbn, commit_csn, commit_time
FROM OPENROWSET (table SYSCOMMITTABLE, db_id (), 0, 0)

--Save the duplicate values INTo the temporary database
INSERT INTO dbChangeTrackingMetadata.dbo.t_syscommittab
SELECT ondisk_ct.* 
FROM sys.syscommittab as ondisk_ct
JOIN dbChangeTrackingMetadata.dbo.t_SYSCOMMITTABLE as inmem_ct
 ON ondisk_ct.xdes_id = inmem_ct.xdes_id

--Delete the duplicate values
DELETE FROM sys.syscommittab
WHERE xdes_id in ( SELECT xdes_id from dbChangeTrackingMetadata.dbo.t_syscommittab )

SET @rowcount = @@rowcount

IF (@rowcount > 0)
BEGIN
  PRINT ''
  PRINT 'DELETED '+CAST(@rowcount as NVARCHAR(10))+' rows from sys.syscommittab that were also stored in SYSCOMMITTABLE'
PRINT ''
END
ELSE
BEGIN
  PRINT ''
  PRINT 'Failed to DELETE DUP rows from sys.syscommittab'
  PRINT ''
END
EXEC sys.sp_setbuildresource 0
GO