镜像相关存储过程

野性酷女 2022-03-18 07:28 168阅读 0赞

共享一个同事整理并内部共享的一个 镜像维护 的相关存储过程。

--===================================================================
    --创建数据库镜像监视器作业，该作业可定期更新服务器实例上每个镜像数据库的镜像状态。
    --sp_dbmmonitoraddmonitoring [ update_period ]
    --update_period
    --指定更新间隔（分钟）。此值可以是介于 1 到 120 分钟之间的值。默认值为 1 分钟。
    --要求具有 sysadmin 固定服务器角色的成员身份运行,更新间隔过小会影响性能
    
    EXEC msdb.sys.sp_dbmmonitoraddmonitoring 3;
    
    
    --===================================================================
    --更改数据库镜像监视参数，设置监视器更新频率
    --sp_dbmmonitorchangemonitoring parameter, value 
    --parameter
    --指定要更改的参数的标识符。当前，只有以下参数可用：
    --1 = 更新周期,数据库镜像状态表的更新间隔期的分钟数。默认间隔为 1 分钟。
    --value
    --为正在更改的参数指定新值。范围在 1 到 120 的整数，用于指定新的更新周期（分钟）。
     
    exec msdb.sys.sp_dbmmonitorchangemonitoring 1,2
    
    
    --===================================================================
    --查看监视器更新频率
    --sp_dbmmonitorhelpmonitoring 
    
    exec msdb.sys.sp_dbmmonitorhelpmonitoring
    
    
    --===================================================================
    --sp_dbmmonitorresults database_name , rows_to_return, update_status 
    
    --database_name
    --指定返回其镜像状态的数据库。
    
    --rows_to_return
    --指定返回的行数：
    --0 = 最后一行
    --1 = 最后两小时的行
    --2 = 最后四小时的行
    --3 = 最后八小时的行
    --4 = 最后一天的行
    --5 = 最后两天的行
    --6 = 最后 100 行
    --7 = 最后 500 行
    --8 = 最后 1,000 行
    --9 = 最后 1,000,000 行
    
    --update_status
    --指定返回结果之前，过程：
    --0 = 不更新数据库的状态。仅使用最后两行计算结果，其保留时间取决于何时刷新状态表。
    --1 = 通过在计算结果之前调用 sp_dbmmonitorupdate 来更新数据库的状态。
    --但是，如果在前 15 秒内已更新状态表，或用户不是 sysadmin 固定服务器角色的成员，
    --则 sp_dbmmonitorresults 将运行，而不更新状态。
    
    EXEC msdb.sys.sp_dbmmonitorresults DB1, 2, 0;
    
    
    
    --===================================================================
    --停止并删除服务器实例上所有数据库的镜像监视器作业。 
    --要求具有 sysadmin 固定服务器角色的成员身份。
    
    EXEC msdb.sys.sp_dbmmonitordropmonitoring
    
    --===================================================================
    --检查dbm_monitor_data中数据是否有超过阀值数据,如果有,则报警
    --status: 数据库的状态：0 = 已挂起 1 = 已断开 2 = 正在同步 3 = 挂起故障转移 4 = 已同步
    --send_queue_size:在主体的发送队列中未发送日志的大小 (KB)。
    --redo_queue_size:镜像中重做队列的大小 (KB)。
    --role：服务器实例的当前镜像角色：1 = 主体 2 = 镜像，
    --witness_status：见证状态：0 = 未知 1 = 已连接 2 = 已断开
    
    use msdb;
    GO
    IF (OBJECT_ID('tempdb.dbo.#MirrorResult') IS NOT NULL)
    BEGIN
    DROP TABLE #MirrorResult
    END
    GO
    WITH tmp AS(
    SELECT 
    ROW_NUMBER()OVER(PARTITION BY Database_id ORDER BY local_time DESC) AS RID,
    *
    FROM msdb.dbo.dbm_monitor_data
    )
    SELECT * INTO #MirrorResult FROM tmp
    WHERE RID=1
    AND (
    ([status]<>2 AND [status]<>4) 
    OR send_queue_size>30000 
    OR redo_queue_size>30000)
    
    --如果表不为空，则镜像可能出现问题
    IF EXISTS(SELECT 1 FROM #MirrorResult)
    BEGIN
    DECLARE @databaseNames NVARCHAR(MAX);
    DECLARE @errorMessage NVARCHAR(MAX);
    SET @databaseNames='';
    SELECT @databaseNames=@databaseNames+DBS.name+'/' FROM #MirrorResult MR
    INNER JOIN master.sys.databases DBS
    ON MR.database_id=DBS.database_id
    
    set @errorMessage= '数据库：'+@databaseNames+' 镜像断开或者存在大量日志为发送或重做'
    
    --发送警告
    PRINT @errorMessage
    
    END
    --===================================================================