JOB 自动检测复制订阅超时

「爱情、让人受尽委屈。」 2022-01-11 11:27 190阅读 0赞

---JOB 自动检测复制订阅超时。并通过邮件的方式通知DBA
    USE [master]
    GO
    
    CREATE TABLE [dbo].[PublicationTokenResult](
    	[ID] [int] IDENTITY(1,1) NOT NULL,
    	[Publication] [nvarchar](200) NULL,
    	[Subscriber] [nvarchar](200) NULL,
    	[SubscriberDB] [nvarchar](200) NULL,
    	[DistributorLatency] [int] NULL,
    	[SubscriberLatency] [int] NULL,
    	[OverallLatency] [int] NULL,
    	[CreatedTime] [datetime] NULL,
    PRIMARY KEY CLUSTERED 
    (
    	[ID] ASC
    )
    )
    
    GO
    
    DECLARE @publication AS NVARCHAR(200);
    DECLARE @delaySeconds INT;
    DECLARE @tokenKeepDays INT;
    DECLARE @recordKeepDays INT;
    SET @tokenKeepDays=3; --令牌保存时间
    SET @recordKeepDays=300; --检测记录保存时间
    SET @publication = N'xxx_agent_analy';
    SET @delaySeconds=60;  --超时报警时间
    
    --=======================================================================
    --检查记录表是否存在，不存在则创建
    IF(NOT EXISTS(
    	SELECT 1 FROM master.sys.tables 
    	WHERE name='PublicationTokenResult' 
    	AND type='U'))
    BEGIN
    CREATE TABLE master.dbo.PublicationTokenResult
    (
    	ID INT IDENTITY(1,1) PRIMARY KEY,
    	Publication NVARCHAR(200),
    	Subscriber NVARCHAR(200),
    	SubscriberDB NVARCHAR(200),
    	DistributorLatency INT,
    	SubscriberLatency INT,
    	OverallLatency INT,
    	CreatedTime DATETIME
    )
    END
    
    --=======================================================================
    --创建token
    DECLARE @tokenID AS INT;
    EXEC sys.sp_posttracertoken 
      @publication = @publication,
      @tracer_token_id = @tokenID OUTPUT;
    
    --=======================================================================
    --等待指定秒数
    DECLARE @SQL NVARCHAR(200);
    SET @SQL='WAITFOR DELAY '''+CONVERT(NVARCHAR(20),DATEADD(SECOND,@delaySeconds,'2001-1-1'),108)+''''
    EXEC(@SQL)
    
    --=======================================================================
    --为每个已插入发布以确定滞后时间的跟踪标记分别返回一行
    CREATE TABLE #tokens (tracer_id int, publisher_commit datetime);
    INSERT #tokens (tracer_id, publisher_commit)
    EXEC sys.sp_helptracertokens @publication = @publication;
    SET @tokenID = (SELECT TOP 1 tracer_id FROM #tokens
    ORDER BY publisher_commit DESC);
    DROP TABLE #tokens;
    
    --=======================================================================
    --获取令牌执行结果
    -- Get history for the tracer token.
    CREATE TABLE #result(
    	DistributorLatency int, 
    	Subscriber nvarchar(200),
    	SubscriberDB nvarchar(200),
    	SubscriberLatency int,
    	OverallLatency int
    	);
    INSERT #result(DistributorLatency,Subscriber,SubscriberDB,SubscriberLatency,OverallLatency)
    EXEC sys.sp_helptracertokenhistory 
      @publication = @publication, 
      @tracer_id = @tokenID;
      
    --=======================================================================
    --保存处理执行结果
    INSERT INTO master.dbo.PublicationTokenResult
    (
    	Publication,
    	Subscriber,
    	SubscriberDB,
    	DistributorLatency,
    	SubscriberLatency,
    	OverallLatency,
    	CreatedTime
    )
    SELECT @publication,
    	Subscriber,
    	SubscriberDB,
    	DistributorLatency,
    	SubscriberLatency,
    	OverallLatency,
    	GETDATE()
    	FROM #result;
    	
    --=======================================================================
    --保存处理执行结果
    
    DECLARE @overallLatency INT
    SELECT @overallLatency=OverallLatency FROM #result
    
    IF((@overallLatency IS NULL)OR(@overallLatency>@delaySeconds))
    BEGIN
    SELECT '超时警告'
    
    
    declare @mailid int
    
    EXECUTE [192.168.0.1,18991].[msdb].[dbo].[sp_send_dbmail]
        @profile_name = 'mail'
        ,@recipients  = 'xxx@xyz.com;wenzhong@xyz.com'
        ,@body        = '计费服务器 3 xxx_agent_analy 复制超时警告'
        ,@subject     = '数据库邮件'
        ,@mailitem_id = @mailid OUTPUT
    
    select @mailid
    
    END
    ELSE
    BEGIN
    SELECT '没有超时'
    END
    
    
    --=======================================================================
    --清理执行结果和删除过期记录
    DROP TABLE #result
    
    DECLARE @date DATETIME;
    SET @date=DATEADD(DAY,@tokenKeepDays,GETDATE())
    
    EXEC sp_deletetracertokenhistory 
    @publication = @publication,
    @cutoff_date = @date
    
    DELETE FROM master.dbo.PublicationTokenResult
    WHERE CreatedTime>DATEADD(DAY,@recordKeepDays,GETDATE())