SQLServer 事务复制订阅节点非活跃状态（inactive）错误的处理：Error in replication::subscription(s) have been marked inactive and must be reinitialized

2024-08-06 20:47 由 MSSQL123 发表于 #数据库

事务复制中订阅节点非活动（ inactive）错误

在SQLServer的事务复制模型中，会出现“订阅过期”的错误，
相关订阅分发代理Job的典型错误如下："Agent SQLNYC01-Onvoices-PubInvoicesInvoices-SQLNYC01-1353 is retrying after an error. 211 retries attempted. See agent job history in the Jobs folder for more details."
Job的详细错误如下： “Error in replication::subscription(s) have been marked inactive and must be reinitialized”这一类错误

1：将事务复制设置为持续运行时，分发代理SQL作业将重试多少次?

参考下，事务分发Job负责从distribution库将日志传递到订阅节点，如果订阅节点不可访问，该Job将持续重试2147483647，这个数字是int类型的最大值，重试的间隔为1分钟，也可以理解为该Job将无限期永久性持续重试。

代理作业的历史日志查询

Select * from Distribution.dbo.MSdistribution_history where [Time] > dateadd(hh,-24,getdate()) and comments like '%is retrying after an error%' order by [Time] desc
or
Select t1.[Time] as 'Logged Time', UPPER(t2.name) as 'Disribution Agent Name',
LTRIM(RTRIM((Replace(Substring(t1.comments, charindex('.',t1.comments), charindex('retries',t1.comments)-charindex('.',t1.comments)),'.','')))) as 'Total Retries Attempted',
Substring(t1.comments, charindex('VM',t1.comments), 4) as 'Subscriber'
from Distribution.dbo.MSdistribution_history t1
Inner Join Distribution.dbo.MSdistribution_agents t2 on t1.agent_id = t2.id
where comments like '%retries attempted%'
and [Time] > dateadd(hh,-24,getdate())
order by 4, t1.[Time] desc

2:分发代理会在无限制连续重试的情况下失败吗?(例如:间歇性网络连接问题)

在这种情况下，分发代理将继续重试2147483647次。然而，在通过了下面的Transaction Retention设置之后，系统将做出反应并导致失败。如下截图（SSMS. Replication Properties打开)

这就是所谓的“分发的事务保留期”。默认是保留72小时，如果Job连续错误重试超过72小时，可能会出现以下故障:

"18854 One or more subscriptions have been marked inactive. Drop and re-create all subscriptions for this node that are failing with this error."
"The subscription(s) have been marked inactive and must be reinitialized. NoSync subscriptions will need to be dropped and recreated. (Source: MSSQLServer, Error number: 21074"

注意上面的72小时这个参数，过期的事务日志数据是通过“Distribution clean up: distribution”这个Job清理的，该Job10分运行一次，通过修改But not more than输入框的参数，可以影响“Distribution clean up: distribution”job中执行脚本的参数@max_distretention这个参数的赋值。但是奇葩的是，如果修改Job中的SQL中的@max_distretention参数，不会联动修改上图中But not more than输入框的数据显示。

这个参数本质上就是将distribution中超过72小时（时间可以自定义）的日志删除。

3:如何监控Replication系统中发生的导致复制中断的错误?

分发数据distribution中记录了事务复制过程中产生的错误日志，设置SQL作业来监视、记录和警告某些错误，并立即修复这些错误，以避免订阅变得中断。

最经典的错误如下

20598 - The row was not found at the Subscriber when applying the replicated command.
2627 - Violation of PRIMARY KEY constraint ‘PK_TBL’. Cannot insert duplicate key in object ‘dbo.TBL’.

利用如下SQL可以监控事务复制中发生的错误信息：

Use Distribution
go

Declare @RepErrorsNew int --check last 5 mins; sql job executes every 5 mins
Select @RepErrorsNew = (select top 1 ID from Distribution.dbo.MSrepl_errors with (nolock)
where error_text like '%The row was not found at the Subscriber when applying the replicated command.%'
and [time] > dateadd(mi,-15,getdate()) order by [time] desc)

If @RepErrorsNew IS NOT NULL and @RepErrorsNew > 0
Begin
　　RAISERROR('Transactional Replication Error Alert - Row Not Found!', 16, 1)
End


Use Distribution
go

Declare @RepErrorsNew int --check last 5 mins; sql job executes every 5 mins
Select @RepErrorsNew = (select top 1 ID from Distribution.dbo.MSrepl_errors with (nolock)
where error_text like '%Violation of PRIMARY KEY constraint%'
and [time] > dateadd(mi,-5,getdate()) order by [time] desc)

If @RepErrorsNew IS NOT NULL and @RepErrorsNew > 0
Begin
　　RAISERROR('Transactional Replication Error Alert - Violation of PRIMARY KEY constraint!', 16, 1)
End

4:在事务性复制中还有其他保留期的设置吗?

是的!它被称为“发布保存期”。要查找此设置，SSMS---复制---本地发布，右键单击发布并选择属性。然后在常规选项卡中找到订阅过期部分。下面的截图很好地解释了这一点。

以下是微软对这两种复制保留期限的说明:
事务复制使用最大分发保留期(sp_adddistributiondb (Transact-SQL)的@ max_distributiontion参数)和发布保留期(sp_addpublication (Transact-SQL)的@retention参数):
如果在最大分发保留期限(默认为72小时)内未同步订阅，并且分发数据库中存在未传递给订阅服务器的更改，则该订阅将被运行在分发服务器上的分发清理作业标记为未激活。必须重新初始化订阅。
如果未在发布保留期限(默认为336小时)内同步订阅，则订阅将过期，并由在发布服务器上运行的过期订阅清理作业删除。必须重新创建并同步订阅。
如果推送订阅过期，它将被完全删除，但拉订阅不会。您必须清除订阅服务器上的拉取订阅。有关详细信息，请参阅“删除拉取订阅”。

5，如何强制激活订阅

某些情况下，订阅端断开超过设置的分发日志最大保留期限之后，订阅将会被标记为inactive，需要 must be reinitialized，此时可以强制“激活”订阅，参考步骤如下：

1. 执行Select * from MSsubscriptions 定位到过期的订阅

2. 使用如下的语句重置MSsubscriptions表. 使用 publisher_id, publisher_db, publication_id, subscriber_id and subscriber_db来激活订阅
update distribution..MSsubscriptions set status=2 where publisher_id='x' andpublisher_db='x' and publication_id='x' and subscriber_id='x' and subscriber_db='x'
上述订阅状态status字段的说明：
0 = Inactive
1 = Subscribed
2 = Active

另外请注意：即便是成功激活了订阅，但是由于分发库distrubution清理掉了过期的日志，导致部分日志不会传递到订阅端，仍旧可能存在数据不一致的情况。

6，如何自动跳过事务复制错误，避免一条错误导致整个复制中断

正如MySQL的主从复制中设置自动跳过复制错误一样，SQLServer也可以设置在事务复制的过程中，如果遇到特定的错误（2061,2627,20598）会自动跳过而不至于导致整个复制中断，同时SQLServer将遇到的错误信息写入distribution.dbo.MSrepl_errors表中，可以通过监控手段来发现错误并修正错误。
2601：Cannot insert duplicate key row in object '%.*ls' with unique index '%.*ls'.)
2627 ：Violation of %ls constraint '%.*ls'. Cannot insert duplicate key in object '%.*ls'.)
20598：The row was not found at the Subscriber when applying the replicated command
参考：https://learn.microsoft.com/en-us/previous-versions/sql/sql-server-2008-r2/ms151331(v=sql.105)

参考链接：

https://www.sanssql.com/2008/05/error-in-replicationsubscriptions-have.html
https://community.dynamics.com/blogs/post/?postid=319c904d-3673-42b5-9452-3bf373f3c391
https://www.techdevops.com/Article.aspx?CID=105