ITPub博客

首页 > 数据库 > SQL Server > Sql Server监控发布订阅复制Replication、subscription的延迟时间和未发送命令行数量

Sql Server监控发布订阅复制Replication、subscription的延迟时间和未发送命令行数量

原创 SQL Server 作者:lusklusklusk 时间:2021-10-29 17:25:53 0 删除 编辑

监控要点
1、采集distribution.dbo.MSrepl_commands显示的复制的命令行数量
2、采集distribution.dbo.MSdistribution_status显示的未发送的命令行数量
3、采集distribution.dbo.sp_replmonitorhelpsubscription显示的延迟时间
4、采集distribution库中MSlogreader_history、MSdistribution_history、MSsnapshot_history、MSrepl_errors、sysreplicationalerts五表显示的报错信息
5、采集distributiondbo库中MSpublications表和订阅库中MSreplication_objects表,两者互相except可以得到哪些表存在发布中但是不存在订阅中,或哪些表存在订阅中但是不存在发布中


当复制的命令行数量超过某个阀值 或 未发送的命令行数量超过某个阀值 或 延迟时间超过某个阀值 或 有报错信息 或 表存在发布中但是不存在订阅中 或 表存在订阅中但是不存在发布中 就报警



--代码如下
USE [DBA]
GO

/****** Crate Date: 2021-10-28  ******/
/****** Author:  Lukes.Liao ******/

CREATE PROCEDURE [dba].[usp_Check_replicaton_undelivery_lantency]

declare @ReplCmd_count int
declare @UndeliveryCmd_count int
declare @Delivery_latency int
declare @error_count1 int
declare @error_count2 int
declare @error_count3 int
declare @error_count4 int
declare @error_count5 int
declare @SubMinusPub_count int
declare @PubMinusSub_count int


--distribution.dbo.MSrepl_commands rows and sizes
IF OBJECT_ID('tempdb..#replication_tempTbl') IS NOT NULL
BEGIN
    DROP TABLE #replication_tempTbl
END

CREATE TABLE #replication_tempTbl
(
    table_name VARCHAR(30)
    ,row_count BIGINT
    ,reserved_space VARCHAR(30)
    ,data_space VARCHAR(30)
    ,index_size VARCHAR(30)
    ,unused_space VARCHAR(30)
)
INSERT INTO #replication_tempTbl
EXEC Wondadb3.distribution..sp_spaceused 'MSrepl_commands'
SELECT @ReplCmd_count=row_count FROM #replication_tempTbl




--replicaton undelivery cmds
select @UndeliveryCmd_count=max(UndelivCmdsInDistDB) from wondadb3.[distribution].[dbo].MSdistribution_status with(nolock)




--replicaton latency
IF OBJECT_ID('tempdb..#replication_tempTb2') is not null
BEGIN
    Drop table #replication_tempTb2
END

CREATE TABLE #replication_tempTb2
(
status int,    
warning int,    
subscriber VARCHAR(100),    
subscriber_db VARCHAR(100),    
publisher_db VARCHAR(100),    
publication    VARCHAR(100),
publication_type int,
subtype int,    
latency    int,
latencythreshold int,    
agentnotrunning    int,
agentnotrunningthreshold int,    
timetoexpiration int,    
expirationthreshold int,    
last_distsync datetime,     
distribution_agentname VARCHAR(100),    
mergeagentname VARCHAR(100),    
mergesubscriptionfriendlyname VARCHAR(100),    
mergeagentlocation    VARCHAR(100),
mergeconnectiontype    int,
mergePerformance int,    
mergerunspeed float,    
mergerunduration int,    
monitorranking int,    
distributionagentjobid binary(16),
mergeagentjobid binary(16),    
distributionagentid    int,
distributionagentprofileid    int,
mergeagentid int,    
mergeagentprofileid    int,
logreaderagentname VARCHAR(100),    
publisher VARCHAR(100)
)

exec Wondadb3.distribution.dbo.sp_replmonitorhelpsubscription @publisher_db ='wondb',@publication_type=0
insert into #replication_tempTb2  
exec Wondadb3.distribution.dbo.sp_replmonitorhelpsubscription @publisher_db ='wondb',@publication_type=0
select @Delivery_latency=max(latency) from #replication_tempTb2 where latency>0  and subscriber='DBPROD131A'



--the lastest 1 hour errors
select @error_count1=count(*) from Wondadb3.[distribution].[dbo].[MSlogreader_history] where error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())
select @error_count2=count(*) from Wondadb3.[distribution].[dbo].[MSdistribution_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())  
select @error_count3=count(*) from Wondadb3.[distribution].[dbo].[MSsnapshot_history] WHERE error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())  
select @error_count4=count(*) from Wondadb3.[distribution].[dbo].MSrepl_errors where error_code!='' and [time] >= DATEADD(HOUR, -1, GETDATE())  
select @error_count5=count(*) from Wondadb3.msdb.dbo.sysreplicationalerts where error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE())


--SubMinusPub_count
select @SubMinusPub_count=count(*) from (
select distinct publisher_db,publication,article from dbprod131a.wondb.dbo.MSreplication_objects where publisher_db='wondb'
except
select a.publisher_db,b.publication,a.article
from dbprod129.[distribution].[dbo].MSarticles a inner join dbprod129.[distribution].[dbo].MSpublications b
on a.publication_id=b.publication_id and a.publisher_db=b.publisher_db and a.publisher_db='wondb') a

--PubMinusSub_count
select @PubMinusSub_count=count(*) from (
select a.publisher_db,b.publication,a.article
from [distribution].[dbo].MSarticles a inner join [distribution].[dbo].MSpublications b
on a.publication_id=b.publication_id and a.publisher_db=b.publisher_db and a.publisher_db='wondb'
except
select distinct publisher_db,publication,article from dbprod131a.wondb.dbo.MSreplication_objects where publisher_db='wondb') a



--If achieve threshhold then send email
if(@ReplCmd_count>50000000 or @UndeliveryCmd_count>300000 or @Delivery_latency>3600 or @error_count1>0 or @error_count2>0 or @error_count3>0 or @error_count4>0 or @error_count5>0  or @SubMinusPub_count>0 or @PubMinusSub_count>0)
begin

DECLARE @Body NVARCHAR(MAX)

--Email Body Part1
SET @Body =
'<html>
<body style="font-family:Arial">
    <tr>
        <b>MSrepl_commands count>50000000 or Undelivery_commands count>300000 or Delivery_latency>3600 or errors will trigger to send this email,DBAS please have a look</b>
    </tr>
    <br> </br>
    <br> </br>
    <tr>
        <b>MSrepl_commands Table Information:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Table_name</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Row_count</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Reserved_space</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Unused_space</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

SELECT
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + table_name + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),row_count) + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + reserved_space + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + unused_space + '</td>
    </tr>
'
from #replication_tempTbl

SET @Body +=
'    </tbody>
</table>
</body>
</html>'



--Email Body Part2
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b>Undelivery Commands Information:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publication_name</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publisher_db</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Table_name</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">UndelivCmds_count</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Subscrip_agent_name</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 10
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + b.publication + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + b.publisher_db + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + c.article + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),a.UndelivCmdsInDistDB) + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + b.name + '</td>
    </tr>
'
from Wondadb3.[distribution].[dbo].MSdistribution_status a with(nolock)  
inner join Wondadb3.[distribution].[dbo].MSdistribution_agents b with(nolock) on a.agent_id=b.id
inner join Wondadb3.[distribution].[dbo].MSarticles c with(nolock) on a.article_id=c.article_id and
b.publisher_db=c.publisher_db and a.UndelivCmdsInDistDB>0
order by UndelivCmdsInDistDB desc

SET @Body +=
'    </tbody>
</table>
</body>
</html>'



--Email Body Part3
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b colspan="4">Latency Information:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publisher_db</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publication_name</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Subscriber_server</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Subscriber_db</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Latency_seconds</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 10
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + publisher_db + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + publication + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + subscriber + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + subscriber_db + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),latency) + '</td>
    </tr>
'
from #replication_tempTb2 where latency>0
and subscriber='DBPROD131A' order by latency desc

SET @Body +=
'    </tbody>
</table>
</body>
</html>'


--Email Body Part4
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b colspan="4">MSlogreader_history Error:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publisher_db</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Time</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Comments</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 3
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + b.publisher_db + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),a.time) + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + a.comments + '</td>
    </tr>
'
from Wondadb3.[distribution].[dbo].[MSlogreader_history] a  inner join Wondadb3.distribution.dbo.MSlogreader_agents b
on a.agent_id=b.id and  a.error_id != 0 AND a.[time] >= DATEADD(HOUR, -1, GETDATE()) order by a.time desc

SET @Body +=
'    </tbody>
</table>
</body>
</html>'


--Email Body Part5
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b colspan="4">MSdistribution_history Error:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publication</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Time</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Comments</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 3
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + b.publication + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),a.time) + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + a.comments + '</td>
    </tr>
'
from Wondadb3.[distribution].[dbo].[MSdistribution_history] a inner join Wondadb3.distribution.dbo.MSdistribution_agents b
on a.agent_id=b.id and a.error_id != 0 AND a.[time] >= DATEADD(HOUR, -1, GETDATE())  order by a.time desc

SET @Body +=
'    </tbody>
</table>
</body>
</html>'


--Email Body Part6
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b colspan="4">MSsnapshot_history Error:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publisher_db</th>
                        <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publication</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Time</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Comments</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 3
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + b.publisher_db + '</td>
                <td style="border:1px solid #ddd; padding:6px 10px">' + b.publication + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),a.time) + '</td>
                <td style="border:1px solid #ddd; padding:6px 10px">' + a.comments + '</td>
    </tr>
'
from Wondadb3.[distribution].[dbo].[MSsnapshot_history] a inner join distribution.dbo.MSsnapshot_agents b
on a.agent_id=b.id and  a.error_id != 0 AND a.[time] >= DATEADD(HOUR, -1, GETDATE())  order by a.time desc

SET @Body +=
'    </tbody>
</table>
</body>
</html>'


--Email Body Part7
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b colspan="4">MSrepl_errors Error:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Time</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Error_text</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 3
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),time) + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + error_text + '</td>
    </tr>
'
from Wondadb3.[distribution].[dbo].MSrepl_errors where error_code!='' and [time] >= DATEADD(HOUR, -1, GETDATE())  order by time desc

SET @Body +=
'    </tbody>
</table>
</body>
</html>'


--Email Body Part8
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b colspan="4">Sysreplicationalerts Error:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publisher_db</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publication</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Subscriber</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Subscriber_db</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Time</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Error_text</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 3
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + publisher_db + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + publication + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + subscriber + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + subscriber_db + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + CONVERT(varchar(50),time) + '</td>
        <td style="border:1px solid #ddd; padding:6px 10px">' + alert_error_text + '</td>  
    </tr>
'
from Wondadb3.msdb.dbo.sysreplicationalerts where error_id != 0 AND [time] >= DATEADD(HOUR, -1, GETDATE()) order by time desc

SET @Body +=
'    </tbody>
</table>
</body>
</html>'


--Email Body Part9
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b colspan="4">Table in subscription but do not in publicaction:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publisher_db</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publication</th>
                        <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Article</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 3
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + publisher_db + '</td>
                <td style="border:1px solid #ddd; padding:6px 10px">' + publication + '</td>
                <td style="border:1px solid #ddd; padding:6px 10px">' + article + '</td>
    </tr>
'
from
(select distinct publisher_db,publication,article from dbprod131a.wondb.dbo.MSreplication_objects where publisher_db='wondb'
except
select a.publisher_db,b.publication,a.article
from dbprod129.[distribution].[dbo].MSarticles a inner join dbprod129.[distribution].[dbo].MSpublications b
on a.publication_id=b.publication_id and a.publisher_db=b.publisher_db and a.publisher_db='wondb') a

SET @Body +=
'    </tbody>
</table>
</body>
</html>'


--Email Body Part10
SET @Body +=
'<html>
<body style="font-family:Arial">
    <br> </br>
    <tr>
        <b colspan="4">Table in publicaction but do not in subscription:</b>
    </tr>
<table style="font-family:Arial; border-collapse: collapse">
    <thead style="font-size:14px; color:#FFF">
        <tr>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publisher_db</th>
            <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Publication</th>
                        <th style="background:#0074D9; border:1px solid #ddd; padding:6px 10px">Article</th>
        </tr>
    </thead>
    <tbody style="font-size:12px; color:#666">
'

select top 3
    @Body +=
'    <tr>
        <td style="border:1px solid #ddd; padding:6px 10px">' + publisher_db + '</td>
                <td style="border:1px solid #ddd; padding:6px 10px">' + publication + '</td>
                <td style="border:1px solid #ddd; padding:6px 10px">' + article + '</td>
    </tr>
'
from
(select a.publisher_db,b.publication,a.article
from [distribution].[dbo].MSarticles a inner join [distribution].[dbo].MSpublications b
on a.publication_id=b.publication_id and a.publisher_db=b.publisher_db and a.publisher_db='wondb'
except
select distinct publisher_db,publication,article from dbprod131a.wondb.dbo.MSreplication_objects where publisher_db='wondb') a

SET @Body +=
'    </tbody>
</table>
</body>
</html>'



PRINT @Body


EXECUTE msdb.dbo.sp_send_dbmail
    @subject = N'Wondadb3 Replication to Dbprod131A Issue Alert',
    @recipients = N'lukes.liao@XX.com',
    @body = @Body,
    @body_format = N'HTML';

end
end
GO



来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30126024/viewspace-2839781/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Welcome to Lukes DB HOME。 Oracle OCM、Mysql OCP, 10年DBA工作经验,博客仅记录自己的一个学习过程,不代表完全准确,如有需要,欢迎转载。

注册时间:2015-02-02

  • 博文量
    471
  • 访问量
    905884