--==================================================== --查看镜像状态 SELECT DB_NAME(database_id) AS DatabaseName, dm.mirroring_role AS MirroringRole, (CASE dm.mirroring_role WHEN 1 THEN ‘主体‘ WHEN 2 THEN ‘镜像‘ END) AS MirroringRoleDesc, dm.mirroring_partner_name AS MirroringPartnerName, (CASE WHEN dm.mirroring_witness_name IS NULL THEN ‘--‘ ELSE dm.mirroring_witness_name END)AS MirroringWitnessName, dm.mirroring_state AS MirroringState, (CASE dm.mirroring_state WHEN 0 THEN ‘已挂起‘ WHEN 1 THEN ‘与其他伙伴断开‘ WHEN 2 THEN ‘正在同步‘ WHEN 3 THEN ‘挂起故障转移‘ WHEN 4 THEN ‘已同步‘ WHEN 5 THEN ‘伙伴未同步‘ WHEN 6 THEN ‘伙伴已同步‘ WHEN NULL THEN ‘无镜像‘ END) AS MirroringStateDesc, dm.mirroring_safety_level AS MirroringSafetyLevel, (CASE dm.mirroring_safety_level WHEN 0 THEN ‘未知‘ WHEN 1 THEN ‘异步‘ WHEN 2 THEN ‘同步‘ WHEN NULL THEN ‘无镜像‘ END) AS MirroringSafetyLevelDesc, dm.mirroring_witness_state AS MirroringWitnessState, (CASE dm.mirroring_witness_state WHEN 0 THEN ‘见证未知‘ WHEN 1 THEN ‘见证连接‘ WHEN 2 THEN ‘见证断开‘ WHEN NULL THEN ‘无见证‘ END) AS MirroringWitnessStateDesc FROM sys.database_mirroring dm WHERE dm.mirroring_guid IS NOT NULL --============================================================ --查看镜像的日志传送 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 将运行，而不更新状态。 --============================================================ --创建数据库镜像监视器作业，该作业可定期更新服务器实例上每个镜像数据库的镜像状态。 sp_dbmmonitoraddmonitoring [ update_period ] update_period：指定更新间隔（分钟）。此值可以是介于 1 到 120 分钟之间的值。默认值为 1 分钟。 --============================================================ --自定义查询 --sp_dbmmonitoraddmonitoring 数据来源于dbm_monitor_data WITH tmp AS( SELECT ROW_NUMBER()OVER( PARTITION BY dm.database_id ORDER BY dm.[local_time] DESC) AS RID, * FROM msdb.dbo.dbm_monitor_data dm) SELECT * FROM tmp WHERE RID=1 -------------------------------------------------------------------------------- --补充资料 --============================================ --MSDN: http://technet.microsoft.com/zh-cn/library/ms173768.aspx --sp_dbmmonitorupdate 会插入镜像相关数据，并将超过天的历史数据删除。
--=================================================================== --创建数据库镜像监视器作业，该作业可定期更新服务器实例上每个镜像数据库的镜像状态。 --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: 数据库的状态：= 已挂起1 = 已断开2 = 正在同步3 = 挂起故障转移4 = 已同步 --send_queue_size:在主体的发送队列中未发送日志的大小(KB)。 --redo_queue_size:镜像中重做队列的大小(KB)。 --role：服务器实例的当前镜像角色：= 主体1 = 镜像， --witness_status：见证状态：= 未知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 --===================================================================
--============================================================================= --查看当前挂起的镜像或有大量日志积压的镜像 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)