Agent Jobs

Put sp_dbmmonitorresults Into a Table

Here’s some SQL logic that will take the results of sp_dbmmonitorresults and put into a table. This will cover ALL mirrored databases on the server.

use master;
set nocount on

declare	@get_mirror_status_of_all_dbs varchar(max)
set @get_mirror_status_of_all_dbs = ''
select	@get_mirror_status_of_all_dbs = @get_mirror_status_of_all_dbs + 'exec msdb..sp_dbmmonitorresults ''' + sd.name + ''';' + CHAR(10)
from	sys.databases sd inner join sys.database_mirroring sdm on sd.database_id = sdm.database_id where	sd.database_id > 4 and sdm.mirroring_guid is not null order by name asc

if OBJECT_ID('tempdb..#mirror_stats') is not null
drop table #mirror_stats
create table #mirror_stats
(
[stat_id] int identity(1,1)
,	[database_name]	sysname
,	[role] int
,	[mirroring_state] int
,	[witness_status] int
,	[log_generation_rate] int
,	[unsent_log] int
,	[send_rate] int
,	[unrestored_log] int
,	[recovery_rate] int
,	[transaction_delay] int
,	[transactions_per_sec] int
,	[average_delay] int
,	[time_recorded] datetime
,	[time_behind] datetime
,	[local_time] datetime
)

insert into #mirror_stats
(
[database_name]
,	[role]
,	[mirroring_state]
,	[witness_status]
,	[log_generation_rate]
,	[unsent_log]
,	[send_rate]
,	[unrestored_log]
,	[recovery_rate]
,	[transaction_delay]
,	[transactions_per_sec]
,	[average_delay]
,	[time_recorded]
,	[time_behind]
,	[local_time]
)

exec	(@get_mirror_status_of_all_dbs)

select * from #mirror_stats

drop table #mirror_stats
Advertisements

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