-- Fonte: http://www.sqlservercentral.com/scripts/alerts/71731/ DECLARE @HTML_Body VARCHAR(MAX) DECLARE @HTML_Head VARCHAR(MAX) DECLARE @HTML_Tail VARCHAR(MAX) 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 log_generation_rate INT NULL , -- in kb / sec unsent_log INT , -- in kb send_rate INT NULL , -- in kb / sec unrestored_log INT , -- in kb recovery_rate INT NULL , -- in kb / sec transaction_delay INT NULL , -- in ms transactions_per_sec INT NULL , -- in trans / sec average_delay INT , -- in ms 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_guid IS NOT NULL 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'msdb.sys.sp_dbmmonitorresults ''' + REPLACE(@MirroredDatabaseName, N'''', N'''''') + N''',0,0' INSERT INTO @MirrorStats EXEC ( @command ) FETCH NEXT FROM @Cursor_MirroredDatabases INTO @MirroredDatabaseName END CLOSE @Cursor_MirroredDatabases DEALLOCATE @Cursor_MirroredDatabases select * from @MirrorStats SET @HTML_Head = '' SET @HTML_Head = @HTML_Head + '
' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + ' ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + '' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Below is a list of Mirrored Databases and their current mirror state.Database | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Role | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Mirror State | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Witness Status | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Log Generation Rate (KB/sec) | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Unsent Log (KB) | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Send Rate (KB/sec) | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Unrestored Log (KB) | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Recovery Rate (KB/sec) | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Transaction Delay (ms) | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Transactions per sec | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Avg Delay (ms) | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Time Recorded | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + 'Time Behind | ' + CHAR(13) + CHAR(10) ; SET @HTML_Head = @HTML_Head + '
---|