获取所有数据库的SQL镜像状态

Get SQL Mirror Status Across All Databases

发布-日期: 2015年08月27日 (评论)

中文
English
SQL Logic
Build Info
Author
License

中文

使用此逻辑可以跨所有数据库获取镜像状态。

English

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

Logic

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