问题描述
在检测到基于保持期的元数据清除操作已针对尚未发送到发布服务器的更改删除了订阅服务器上的元数据之后,合并代理失败。 您必须重新初始化订阅(无需上载)。自此订阅服务器上一次同步以来,已过去了0个保持期单位。如果该值小于保持期5,则说明上一次同步是在保持期内完成的,SQL Server不应引发此错误。虚假错误指示复制基础结构问题。
背景描述
Alarm数据库做一中心到多机房的合并发布,中心汇聚各机房的Alarm数据库的数据,通过机房标识字段进行数据筛选,比如某机房ID为1,则中心Alarm数据库只和该机房同步ID为1的数据。
SQL Server官方错误解释文档
合并复制如何管理订阅过期和元数据清除:https://docs.microsoft.com/zh-cn/previous-versions/sql/sql-server-2008/ms151778(v=sql.100)?redirectedfrom=MSDN
sysmergesubscriptions系统复制表资料:https://docs.microsoft.com/zh-cn/sql/relational-databases/system-tables/sysmergesubscriptions-transact-sql?view=sql-server-ver15
解决措施
在中心报警数据库上进行如下操作(此解决方案根据https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e6124f37-1a3a-4cda-9584-3ebcb229b8c6/quotthe-merge-agent-failed-after-detecting-that-retentionbased-metadata-cleanup-has-deleted该网址进行分析得出)
在中心报警数据库上进行如下操作:
修改系统复制表,本来的值是1,修改为0。
/****** SSMS 的 SelectTopNRows 命令的脚本 ******/
SELECT TOP (1000) [subscriber_server]
,[db_name]
,[pubid]
,[datasource_type]
,[subid]
,[replnickname]
,[replicastate]
,[status]
,[subscriber_type]
,[subscription_type]
,[sync_type]
,[description]
,[priority]
,[recgen]
,[recguid]
,[sentgen]
,[sentguid]
,[schemaversion]
,[schemaguid]
,[last_validated]
,[attempted_validate]
,[last_sync_date]
,[last_sync_status]
,[last_sync_summary]
,[metadatacleanuptime]
,[partition_id]
,[cleanedup_unsent_changes]
,[replica_version]
,[supportability_mode]
,[application_name]
,[subscriber_number]
,[last_makegeneration_datetime]
,[last_local_sentgen]
,[last_local_recguid]
,[last_local_recgen]
FROM [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-VM2VQPVRP2T'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-VM2VQPVRP2T'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-IHCLG2EQSEV'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-S4OKPT6B7O7'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-PG14ACL9K1S'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-111BCE8TTVB'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-TL454LDR88O'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-R2SHSC6EC9F'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-12IHAHEOIFF'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-NF0T3HVB1J9'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-MTGDBASJ5SH'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-7VMOUA83BG2'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-3864HL66LCN'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-V98P1G3K7QQ'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-4UUNV9MNC1F'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-QDI7S1DG589'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-HVSMVGMBA55'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-7B3223QRRPP'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-43N2VOL5FO3'
select [cleanedup_unsent_changes] from [Alarm].[dbo].[sysmergesubscriptions] where subscriber_server='WIN-VLODNSMCBF4'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-VM2VQPVRP2T'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-IHCLG2EQSEV'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-S4OKPT6B7O7'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-PG14ACL9K1S'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-111BCE8TTVB'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-TL454LDR88O'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-R2SHSC6EC9F'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-12IHAHEOIFF'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-NF0T3HVB1J9'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-MTGDBASJ5SH'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-7VMOUA83BG2'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-3864HL66LCN'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-V98P1G3K7QQ'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-4UUNV9MNC1F'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-QDI7S1DG589'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-HVSMVGMBA55'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-7B3223QRRPP'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-43N2VOL5FO3'
update [Alarm].[dbo].[sysmergesubscriptions] set cleanedup_unsent_changes=0 where subscriber_server='WIN-VLODNSMCBF4'