Sql Server監控釋出訂閱複製Replication、subscription的延遲時間和未傳送命令列數量

監控要點
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