-- 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.
' + CHAR(13) + CHAR(10) ;
SET @HTML_Head = @HTML_Head + 'Roles: 1 = Principal, 2 = Mirror
' + CHAR(13) + CHAR(10) ;
SET @HTML_Head = @HTML_Head + 'Mirror State: 0 = Suspended, 1 = Disconnected, 2 = Synchronizing, 3 = Pending Failover, 4 = Synchronized
' + CHAR(13) + CHAR(10) ;
SET @HTML_Head = @HTML_Head + 'Witness State: 0 = n/a, 1 = Connected, 2 = Disconnected
' + 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 + ' | 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 + '
' + CHAR(13) + CHAR(10) ;
SET @HTML_Tail = '
' ;
SET @HTML_Body = @HTML_Head + ( SELECT database_name AS [TD] ,
role AS [TD] ,
mirroring_state AS [TD] ,
witness_status AS [TD] ,
log_generation_rate AS [TD] ,
unsent_log AS [TD] ,
send_rate AS [TD] ,
unrestored_log AS [TD] ,
recovery_rate AS [TD] ,
transaction_delay AS [TD] ,
transactions_per_sec AS [TD] ,
average_delay AS [TD] ,
time_recorded AS [TD] ,
time_behind AS [TD]
FROM @MirrorStats
ORDER BY database_name
FOR
XML RAW('tr') ,
ELEMENTS
) + @HTML_Tail
--#### Send the finished Email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'XXXX',
@recipients = 'Seu email',
@subject = 'DMB: Current Mirror Status (All Databases)',
@body = @HTML_Body,
@body_format = 'HTML' ;