SQL Stuff

Get The Status Of Mirrored Databases With This Quick Script

Here’s some quick sql logic to show you the state of your Mirrored database.

use msdb;
set nocount on

declare @monitorresults 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
exec sp_dbmmonitorresults
@database_name	= 'MyDatabase'
,	@mode = 0
,	@update_table	= 0;

select
'server' = @@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