检测复制订阅进度v2

– =============================================
– Author:
– Create date: <2016-06-29> 通过链接服务器监控sqlserver复制与订阅进度（本脚本在“推送订阅”模式下验证正常，其它模式未有验证）
– 系统视图记录的表记录数有时不准（但较少遇到），可使用“DBCC UPDATEUSAGE(‘数据库名’,‘表名’) WITH count_rows”，“UPDATE STATISTICS 表名 索引名” 更新之
– Description: 检测复制订阅进度
– =============================================
DECLARE @sql varchar(max);
DECLARE @dist_linksvr varchar(64); --分发服务器(链接服务名称,下同)
DECLARE @publisher_linksvr varchar(64),@publisher_db varchar(64); --发布服务器及发布数据库
DECLARE @subscriber_linksvr varchar(64),@destination_db varchar(64); --订阅服务器及订阅数据库

–修改参数
SELECT @dist_linksvr = ‘分发服务器的链接服务器名称’,@publisher_linksvr = ‘发布服务器的链接服务器名称’,@publisher_db=‘发布数据库名称’,@subscriber_linksvr = ‘订阅服务器的链接服务器名称’,@destination_db=‘订阅数据库名称’;

–以下正式执行，不要修改
set @sql=‘;with cte_partcolumn AS (
SELECT
schema_name (t.schema_id) schema_name,t.name table_name,c.name partition_column
FROM [’+@publisher_linksvr+‘].[’+@publisher_db+‘].sys.tables t
INNER JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].sys.index_columns ic
ON t.object_id=ic.object_id
INNER JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].sys.columns c
ON ic.object_id = c.object_id AND ic.column_id = c.column_id
WHERE ic.partition_ordinal = 1 AND ic.index_id<=1
),cte_distrib AS (
SELECT
mp.publication_id,mp.publication,mp.publication_type,ma.article_id,ma.article,mp.sync_method,
mp.publisher_id,srv.name publisher_server,mp.publisher_db,ma.source_owner sour_schema,t.name sour_table,
sa.filter_clause,sa.dest_owner dest_schema,sa.dest_table dest_table
,six.rows sour_rows
FROM [’+@dist_linksvr+‘].[distribution].dbo.MSpublications mp
LEFT JOIN [’+@dist_linksvr+‘].[distribution].dbo.MSarticles ma
ON ma.publication_id=mp.publication_id
INNER JOIN [’+@dist_linksvr+‘].[master].sys.servers srv
ON mp.publisher_id = srv.server_id
LEFT JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].dbo.sysarticles sa
ON sa.artid = ma.article_id --AND sa.pubid = ma.publication_id --有差异?
INNER JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].sys.tables t
ON t.object_id = sa.objid
LEFT JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].sys.indexes ix
ON ix.object_id =sa.objid AND isnull(substring(ix.filter_definition,2,len(ix.filter_definition)-2),’‘’‘)=isnull(CAST(sa.filter_clause AS varchar(1024)),’‘’‘) AND ix.index_id <= 1
LEFT JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].sys.sysindexes six
ON sa.objid=six.id AND six.indid = isnull(ix.index_id,1)-- AND six.indid <= 1
),cte_pub AS (
SELECT
ma.publication_id,ma.article_id,ma.article,
ma.source_owner sour_schema,ma.source_object sour_table,
sa.pre_creation_cmd,
six.rows sour_rows
FROM [’+@dist_linksvr+‘].[distribution].dbo.MSarticles ma
INNER JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].dbo.sysarticles sa
ON sa.artid = ma.article_id --AND sa.pubid = ma.publication_id --有差异?
INNER JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].sys.sysindexes six
ON sa.objid=six.id AND six.indid<=1
),cte_repl AS (
SELECT
ma.publication_id,ma.article_id,ma.article,
sa.dest_owner dest_schema,sa.dest_table dest_table,
ms.subscriber_id,srv.name subscriber_server,ms.subscriber_db,ms.subscription_type,ms.sync_type,ms.status,subscription_time,CAST(ms.agent_id AS varchar) agent_id,
six.rows dest_rows
FROM [’+@dist_linksvr+‘].[distribution].dbo.MSarticles ma
INNER JOIN [’+@publisher_linksvr+‘].[’+@publisher_db+‘].dbo.sysarticles sa
ON sa.artid = ma.article_id --AND sa.pubid = ma.publication_id --有差异?
INNER JOIN [’+@dist_linksvr+‘].[distribution].dbo.MSsubscriptions ms
ON ms.article_id=ma.article_id --ms.publication_id=ma.publication_id
INNER JOIN [’+@dist_linksvr+‘].[master].sys.servers srv
ON ms.subscriber_id = srv.server_id
INNER JOIN [’+@subscriber_linksvr+‘].[’+@destination_db+‘].sys.schemas sch
ON sch.name=sa.dest_owner
INNER JOIN [’+@subscriber_linksvr+‘].[’+@destination_db+‘].sys.tables t
ON sch.schema_id =t.schema_id AND t.name=sa.dest_table
INNER JOIN [’+@subscriber_linksvr+‘].[’+@destination_db+‘].sys.sysindexes six
ON t.object_id=six.id AND six.indid<=1
),cte_agent AS (
SELECT
j.job_id,j.name job_name,
reverse(substring(reverse(j.name),1,charindex(’‘-’‘,reverse(j.name)) -1)) agent_id, --agent_id存在关联缺陷（本处从job名称解析agent_id，可从job命令读取agent_id）；应根据subscription_type连接不同服务器（本处为推送订阅）
CASE WHEN ja.stop_execution_date is null then ‘‘running’’ ELSE null END agent_status
FROM [’+@dist_linksvr+‘].msdb.dbo.sysjobs j
LEFT JOIN (SELECT job_id,start_execution_date,stop_execution_date,row_number() over(partition by job_id order by session_id desc) rn FROM [’+@dist_linksvr+‘].msdb.dbo.sysjobactivity /WHERE run_requested_date >= getdate()-10/) ja --跟进观察
ON j.job_id = ja.job_id
WHERE ja.rn = 1
AND j.category_id in (10) --10分发,15快照
)
SELECT
d.publisher_server+’‘.’‘+d.publisher_db publisher,
d.publication_id pub_id,
d.publication_type pub_type, – 0 事务 1 快照 2 Merge。
d.publication,d.sync_method,
d.article_id art_id,d.article,
d.sour_schema+’‘.’‘+d.sour_table pub_table,replace(CAST(isnull(d.filter_clause,cp.partition_column) AS varchar(128)) ,’’ 00:00:00.000’‘,’‘’‘) filter_clause,
CASE WHEN w.sour_rows=d.sour_rows then CAST(w.sour_rows AS varchar) ELSE CAST(w.sour_rows AS varchar)+’‘|’‘+CAST(d.sour_rows AS varchar) END pub_num ,
r.subscriber_server+’‘.’‘+r.subscriber_db subscriber,
r.dest_schema+’‘.’'+r.dest_table repl_table,r.dest_rows repl_num,
d.sour_rows-r.dest_rows diff, --复制订阅差异
w.pre_creation_cmd pre_cmd, --0 none,1 drop,2 delete,3 truncate
r.subscription_type sub_type, --0 推送 1 请求 2 匿名
r.sync_type, – 1 自动 2 不同步
r.status, – 0 不活动 1 已订阅 2 活动
r.subscription_time,
ag.agent_status
FROM cte_distrib d
LEFT JOIN cte_partcolumn cp
ON cp.table_name=d.article
FULL JOIN cte_pub w
ON d.publication_id=w.publication_id AND d.article_id=w.article_id --发布
FULL JOIN cte_repl r
ON d.publication_id=r.publication_id AND d.article_id=r.article_id --订阅意外缺少订阅进程需要重建订阅
LEFT JOIN cte_agent ag
ON ag.agent_id = r.agent_id
WHERE 1=1
–AND d.publication like ‘‘pub_orders%’’ --发布名称
–AND w.sour_table LIKE ‘‘bc_re%’’ --显示特定表
–AND w.sour_rows<>r.dest_rows --仅显示数据同步不一致的
order by d.publisher_server,r.subscriber_server,d.sour_schema,w.sour_table
’
PRINT (@sql);
EXEC (@sql);