SQL Stuff

Get SQL Mirror Status Across All Databases

Use this logic to get the Mirrored Status across all databases.


use master;
set nocount on

declare @get_database_mirroring_state varchar(max)
set @get_database_mirroring_state = ''
select @get_database_mirroring_state = @get_database_mirroring_state + 'declare @monitorresults_' + cast(database_id as varchar(2)) + ' as table (
database_name varchar(255)
, role int
, mirror_state tinyint
, witness_status tinyint
, log_generat_rate int
, unsent_log int
, sent_rate int
, unrestored_log int
, recovery_rate int
, transaction_delay int
, transaction_per_sec int
, average_delay int
, time_recorded datetime
, time_behind datetime
, local_time datetime
);

insert into @monitorresults_' + cast(database_id as varchar(2)) + ' exec msdb..sp_dbmmonitorresults
@database_name = ''' + upper(DB_NAME(database_id)) + '''
, @mode = 0
, @update_table = 0;

select
''server'' = upper(@@servername)
, database_name
, ''role'' =
case role
when ''1'' then ''Principal''
when ''2'' then ''Mirror''
end
, average_delay
, ''mirror_state'' =
case mirror_state
when ''0'' then ''Suspended''
when ''1'' then ''Disconnected''
when ''2'' then ''Synchronizing''
when ''3'' then ''Pending Failover''
when ''4'' then ''Synchronized''
end

Advertisements

Categories: SQL Stuff

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s