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
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30126024/viewspace-2839781/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Sql Server實時監控釋出訂閱複製Replication、subscription有多少延遲的方法SQLServer
- 使用zabbix監控sql server的釋出訂閱SQLServer
- sqlserver關於釋出訂閱replication_subscription的總結SQLServer
- Mysql 建立心跳錶來監控Replication的Slave是否延遲MySql
- SQL Server誤區 一個例項多個映象和日誌傳送延遲SQLServer
- RabbitMQ釋出訂閱實戰-實現延時重試佇列MQ佇列
- SQL Server雙機熱備之釋出、訂閱實現實時同步SQLServer
- PostgreSQL中的複製延遲SQL
- mqtt訂閱和釋出MQQT
- LightDB訂閱和釋出
- MySQL 8 複製（三）——延遲複製與部分複製MySql
- mysql主從延遲複製MySql
- Redis的訊息釋出和訂閱Redis
- Laravel 在事件監聽中實現佇列的方法以及指定加入的佇列名稱和佇列延遲時間Laravel事件佇列
- MySQL中slave監控的延遲情況分析MySql
- 小程式傳送訂閱訊息
- 實現簡單延遲佇列和分散式延遲佇列佇列分散式
- SQLServer 2012複製訂閱資料訂閱過程SQLServer
- 釋出訂閱EventEmitterMIT
- 釋出訂閱模式模式
- openGauss 釋出訂閱
- Redis釋出訂閱Redis
- 迄今微軟不同時期釋出的SQL Server各版本之間的大致區別，供參考查閱微軟SQLServer
- SQL Server中的日期和時間：DATEADD（）SQLServer
- MySQL並行複製延時時間不準確MySql並行
- php訂單延時處理-延時佇列PHP佇列
- hyperf 實現延時佇列,處理超時未支付訂單佇列
- Redis 的訂閱與釋出Redis
- js訊息訂閱和釋出實現元件之間通訊JS元件
- 設計模式之釋出訂閱模式(2) Redis 釋出/訂閱模式設計模式Redis
- [Kogel.Subscribe.Mssql]SQL Server增量訂閱，資料庫變更監聽SQLServer資料庫
- MySQL之 從複製延遲問題排查MySql
- MySQL主從複製延遲解決方案MySql
- 監控 SQL Server 的執行狀況SQLServer
- JS訂閱釋出模式JS模式
- 釋出訂閱管道化
- openGauss-釋出訂閱
- mysql同步(複製)延遲的原因及解決方案MySql