SET NOCOUNT ON
EXEC msdb.sys.sp_dbmmonitorupdate
DECLARE @Cursor_MirroredDatabases CURSOR
DECLARE @command CHAR(256)
DECLARE @MirroredDatabaseName NVARCHAR(128)
DECLARE @MirrorStats TABLE
(
database_name SYSNAME , -- Name of database
role TINYINT , -- 1 = Principal, 2 = Mirror
mirroring_state TINYINT , -- 0 = Suspended, 1 = Disconnected, 2 = Synchronizing, 3 = Pending Failover, 4 = Synchronized
witness_status TINYINT , -- 1 = Connected, 2 = Disconnected ,0=unknown
log_generation_rate INT NULL , -- in kb / sec 自上次更新此数据库的镜像状态以来生成的日志量KB/秒
unsent_log INT , -- in kb 在主体的发送队列中未发送日志的大小 (KB)
send_rate INT NULL , -- in kb / sec 从主体向镜像发送日志的速度KB/秒
unrestored_log INT , -- in kb 镜像中重做队列的大小 (KB)
recovery_rate INT NULL , -- in kb / sec 镜像中的重做速度（KB/秒）
transaction_delay INT NULL , -- in ms 所有事务的总计延迟（毫秒）
transactions_per_sec INT NULL , -- in trans / sec 在主体服务器实例上每秒发生的事务数
average_delay INT , -- in ms 高安全模式下由于数据库镜像的原因，每个事务在主体服务器实例上的平均延迟,高性能模式下为0
time_recorded DATETIME , --数据库镜像监视器记录行的时间
time_behind DATETIME , --镜像数据库当前跟踪的主体的近似系统时钟时间
local_time DATETIME -- Added for UI --更新此行时本地服务器实例上的系统时钟时间
)
SET @Cursor_MirroredDatabases = CURSOR FAST_FORWARD
FOR
SELECT DB_NAME(database_id) AS [DatabaseName] FROM sys.database_mirroring WHERE mirroring_role =1
OPEN @Cursor_MirroredDatabases
FETCH NEXT FROM @Cursor_MirroredDatabases
INTO @MirroredDatabaseName


WHILE @@FETCH_STATUS = 0
BEGIN
--#### Run the monitor (and update the main table)
SET @command = N'sys.sp_dbmmonitorresults ''' + REPLACE(@MirroredDatabaseName, N'''', N'''''') + N''',0,0';
INSERT INTO @MirrorStats
execute ( @command );
FETCH NEXT FROM @Cursor_MirroredDatabases
INTO @MirroredDatabaseName
END
CLOSE @Cursor_MirroredDatabases
DEALLOCATE @Cursor_MirroredDatabases;
select ISNULL(AVG(transaction_delay),0) mirrordelay from @MirrorStats;