在 SQL Server 中复制 sys.syscommittab 表中的键行

项目
03/29/2024

本文提供有关解决可能导致文件中出现重复行sys.syscommittab的SQL Server 更改跟踪问题的信息。

原始产品版本：SQL Server 2008 及更高版本
原始 KB 编号： 3083381

症状

在 Microsoft SQL Server 中比较内存SYSCOMMITTABLE中和磁盘sys.syscommittab上的文件时，可能会看到重复的键行。这些重复值可能会导致备份和检查点操作失败。

“无法在具有唯一索引'si_xdes_id'的对象'sys.syscommittab'中插入重复键行。重复键值 (KeyValue) 。
错误：3999，严重性：17，状态：1。
由于错误 2601，未能将提交表刷新到 dbidDatabaseID 中的磁盘。有关详细信息，请查看错误日志。”

原因

出现此问题的原因是SQL Server更改跟踪中的已知问题。

解决导致重复密钥的因素

若要解决导致重复密钥的因素，请根据你的情况应用以下修复之一：

尽管这些修复可防止重复键行继续显示，但它们不会自动删除重复行。如果不删除重复行，受影响的数据库将无法完成数据库检查点，并且备份可能会失败。

禁用并启用更改跟踪以删除重复行

对受影响的表和数据库禁用更改跟踪。
发出手动数据库检查点。
对受影响的数据库和表启用更改跟踪。

有关更改跟踪的详细信息，请参阅启用和禁用更改跟踪。有关发出手动检查点，请参阅 CHECKPOINT (Transact-SQL) 。

手动删除重复行

将 Transact-SQL 脚本部分的 Transact-SQL 脚本 复制到文本编辑器中。
在 <AFFECTED_DB> 脚本中找到占位符，并将其替换为受影响数据库的名称。
将修改后的脚本作为.sql文件保存到硬盘。例如，C:\temp\remove_duplicates.sql。

如果使用 SQL Server 2014 及更高版本，则必须向和 mssqlsystemresource.mdf 文件授予每个服务 SID 的完全控制权mssqlsystemresource.ldf。为此，请按照下列步骤操作：

导航到与实例 ID 对应的 Bin 目录。例如：
C:\Program Files\Microsoft SQL Server\<Instance ID>\MSSQL\Binn
打开和 mssqlsystemresource.mdf的属性mssqlsystemresource.ldf，然后选择“安全性”选项卡。
找到每个服务 SID 的SQL Server服务，并记下默认权限：
- *Read & execute
- *Read
为每个服务 SID 授予SQL Server服务完全控制权限，然后关闭权限对话框。
在单用户模式下启动SQL Server。有关详细信息，请参阅在单用户模式下启动SQL Server。

注意

当SQL Server在单用户模式下启动时，它将跳过属于Always On可用性组 (AG) 的数据库的启动过程。如果需要排查需要在单用户模式下启动SQL Server的更改跟踪问题，并且启用了更改跟踪的数据库也是 AG 的一部分，则必须在单用户模式下启动SQL Server之前从 AG 中删除数据库，以便数据库联机。
sqlcmd使用命令行连接到专用管理员连接 (DAC) 下的SQL Server，并执行修改后的 Transact-SQL 脚本。例如：
```
sqlcmd -S PRODSERV1\MSSQLSERVER -A -E -i c:\temp\remove_duplicates.sql
```
在多用户模式下重启SQL Server，然后验证针对受影响数据库的备份和 CHECKPOINT 操作是否已成功完成。如果使用了步骤 4，还原对默认值的权限。

Transact-SQL 脚本

--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

在 SQL Server 中复制 sys.syscommittab 表中的键行

症状

原因

解决导致重复密钥的因素

禁用并启用更改跟踪以删除重复行

手动删除重复行

Transact-SQL 脚本

反馈

反馈

其他资源