The meta data is from an SQL 2012 Server.
I have posted alot more, find the whole list here.
Goto Definition or MetaData
Definition:
sys.sp_help_log_shipping_monitor(bit @verbose)MetaData:
create procedure sys.sp_help_log_shipping_monitor ( @verbose bit = 0 ) as begin set nocount on declare @retcode int ,@primary_id uniqueidentifier ,@primary_server sysname ,@primary_database sysname ,@time_since_last_backup int ,@last_backup_utc datetime ,@last_backup_file nvarchar(500) ,@backup_threshold int ,@is_backup_alert_enabled bit ,@secondary_id uniqueidentifier ,@secondary_server sysname ,@secondary_database sysname ,@restore_threshold int ,@is_restore_alert_enabled bit ,@last_copied_file nvarchar(500) ,@last_copied_utc datetime ,@time_since_last_copy int ,@last_restored_file nvarchar(500) ,@last_restored_utc datetime ,@time_since_last_restore int ,@last_restored_latency int ,@prev_primary_server sysname ,@prev_primary_database sysname ,@monitor_server sysname ,@monitor_server_security_mode int ,@is_monitor_local bit ,@curutcdate datetime ,@linkcmd nvarchar(4000) -- -- security check -- exec @retcode = sys.sp_MSlogshippingsysadmincheck if (@retcode != 0 or @@error != 0) return 1 -- -- must be invoked from master db -- if (db_name() != N'master') begin raiserror(5001, 16,-1) return 1 end -- -- create the resultset table -- create table #log_shipping_monitor ( status bit null ,is_primary bit not null default 0 ,server sysname ,database_name sysname ,time_since_last_backup int null ,last_backup_file nvarchar(500) null ,backup_threshold int null ,is_backup_alert_enabled bit null ,time_since_last_copy int null ,last_copied_file nvarchar(500) null ,time_since_last_restore int null ,last_restored_file nvarchar(500) null ,last_restored_latency int null ,restore_threshold int null ,is_restore_alert_enabled bit null ,ts timestamp not null ,primary key (is_primary, server, database_name) ,unique (ts) ) if (@@error != 0) return 1 -- -- create other tables we will use -- create table #secondary_monitor ( secondary_server sysname not null, secondary_database sysname not null, secondary_id uniqueidentifier not null, primary_server sysname not null, primary_database sysname not null, restore_threshold int not null, threshold_alert int not null, threshold_alert_enabled bit not null, last_copied_file nvarchar(500) null, last_copied_date datetime null, last_copied_date_utc datetime null, last_restored_file nvarchar(500) null, last_restored_date datetime null, last_restored_date_utc datetime null, last_restored_latency int null, history_retention_period int not null, primary key (secondary_id, secondary_database) ) if (@@error != 0) return 1 create table #primary_monitor ( primary_id uniqueidentifier primary key not null, primary_server sysname not null, primary_database sysname not null, backup_threshold int not null, threshold_alert int not null, threshold_alert_enabled bit not null, last_backup_file nvarchar(500) null, last_backup_date datetime null, last_backup_date_utc datetime null, history_retention_period int not null, unique (primary_server, primary_database) ) if (@@error != 0) return 1 -- -- get current time -- select @curutcdate = getutcdate() -- -- Enumerate the primary entries -- declare #hcprimaries cursor local fast_forward for select primary_id ,primary_server ,primary_database ,backup_threshold ,threshold_alert_enabled ,last_backup_file ,last_backup_date_utc from msdb.dbo.log_shipping_monitor_primary with (nolock) order by primary_server, primary_database open #hcprimaries fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled, @last_backup_file, @last_backup_utc while (@@fetch_status != -1) begin -- -- we have a primary entry -- select @time_since_last_backup = datediff(minute, @last_backup_utc, @curutcdate) insert into #log_shipping_monitor ( status ,is_primary ,server ,database_name ,time_since_last_backup ,last_backup_file ,backup_threshold ,is_backup_alert_enabled) values ( case when ( @time_since_last_backup > @backup_threshold) then 1 else 0 end ,1 ,@primary_server ,@primary_database ,@time_since_last_backup ,@last_backup_file ,@backup_threshold ,@is_backup_alert_enabled) -- -- process secondaries -- if (upper(@primary_server) = upper(@@servername)) begin -- -- we are on primary server -- get monitor server information -- select @monitor_server = monitor_server ,@monitor_server_security_mode = monitor_server_security_mode from msdb.dbo.log_shipping_primary_databases where primary_id = @primary_id select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end -- -- enumerate the secondaries listed on primary -- declare #hcprimarysecondaries cursor local fast_forward for select secondary_server, secondary_database from msdb.dbo.log_shipping_primary_secondaries with (nolock) where primary_id = @primary_id open #hcprimarysecondaries fetch #hcprimarysecondaries into @secondary_server, @secondary_database while (@@fetch_status != -1) begin -- -- add this primary secondary to result set -- insert into #log_shipping_monitor (is_primary ,server, database_name) values (0, @secondary_server, @secondary_database) select @secondary_id = NULL -- -- Enumerate this secondary from msdb.dbo.log_shipping_monitor_secondary -- if (@is_monitor_local = 1) begin -- -- local monitor -- select @secondary_id = secondary_id ,@restore_threshold = restore_threshold ,@is_restore_alert_enabled = threshold_alert_enabled ,@last_copied_file = last_copied_file ,@last_copied_utc = last_copied_date_utc ,@last_restored_file = last_restored_file ,@last_restored_utc = last_restored_date_utc ,@last_restored_latency = last_restored_latency from msdb.dbo.log_shipping_monitor_secondary where primary_server = upper(@primary_server) and primary_database = @primary_database and secondary_server = upper(@secondary_server) and secondary_database = @secondary_database end -- local monitor else begin -- -- remote monitor -- if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01)) begin -- -- execute as proxy -- exec @retcode = sys.sp_MSproxylogshippingmonitorhelpsecondary @monitor_server = @monitor_server ,@p1 = @primary_server ,@p2 = @primary_database ,@p3 = @secondary_server ,@p4 = @secondary_database ,@p5 = @secondary_id output ,@p6 = @restore_threshold output ,@p7 = @is_restore_alert_enabled output ,@p8 = @last_copied_file output ,@p9 = @last_copied_utc output ,@p10 = @last_restored_file output ,@p11 = @last_restored_utc output ,@p12 = @last_restored_latency output end else begin delete #secondary_monitor select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_secondary ' ,@retcode = 0 begin try insert into #secondary_monitor exec @retcode = @linkcmd @secondary_server = @secondary_server ,@secondary_database = @secondary_database end try begin catch select @retcode = 1 ,@secondary_id = NULL end catch if (@retcode = 0) begin select @secondary_id = secondary_id ,@restore_threshold = restore_threshold ,@is_restore_alert_enabled = threshold_alert_enabled ,@last_copied_file = last_copied_file ,@last_copied_utc = last_copied_date_utc ,@last_restored_file = last_restored_file ,@last_restored_utc = last_restored_date_utc ,@last_restored_latency = last_restored_latency from #secondary_monitor where upper(primary_server) = upper(@primary_server) and primary_database = @primary_database end else begin raiserror(32031, 10, 1, @secondary_server, @secondary_database, @monitor_server) end end end -- remote monitor -- -- do we have data on this secondary -- if (@secondary_id is not null) begin -- -- yes we do - update the entry -- select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate) ,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate) update #log_shipping_monitor set status = case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end ,time_since_last_copy = @time_since_last_copy ,last_copied_file = @last_copied_file ,time_since_last_restore = @time_since_last_restore ,last_restored_file = @last_restored_file ,last_restored_latency = @last_restored_latency ,restore_threshold = @restore_threshold ,is_restore_alert_enabled = @is_restore_alert_enabled where upper(server) = upper(@secondary_server) and database_name = @secondary_database end -- update secondary data -- -- fetch next primary secondary -- fetch #hcprimarysecondaries into @secondary_server, @secondary_database end close #hcprimarysecondaries deallocate #hcprimarysecondaries end -- we are on primary server processing primaries else begin -- -- we are on monitor server -- get details of the secondaries from msdb.dbo.log_shipping_monitor_secondary -- if the same monitor is being used by secondaries -- insert into #log_shipping_monitor ( status ,is_primary ,server ,database_name ,time_since_last_copy ,last_copied_file ,time_since_last_restore ,last_restored_file ,last_restored_latency ,restore_threshold ,is_restore_alert_enabled) select case when (datediff(minute, last_restored_date_utc, @curutcdate) > restore_threshold or last_restored_latency > restore_threshold) then 1 else 0 end ,0 ,secondary_server ,secondary_database ,datediff(minute, last_copied_date_utc, @curutcdate) ,last_copied_file ,datediff(minute, last_restored_date_utc, @curutcdate) ,last_restored_file ,last_restored_latency ,restore_threshold ,threshold_alert_enabled from msdb.dbo.log_shipping_monitor_secondary with (nolock) where primary_server = upper(@primary_server) and primary_database = @primary_database end -- we are on monitor server processing primaries fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled, @last_backup_file, @last_backup_utc end -- while cursor for hcprimaries close #hcprimaries deallocate #hcprimaries -- -- Enumerate the secondary entries -- minus existing secondary entries in resultset -- declare #hcsecondaries cursor local fast_forward for select secondary_server ,secondary_database ,secondary_id uniqueidentifier ,primary_server ,primary_database ,restore_threshold ,threshold_alert_enabled ,last_copied_file ,last_copied_date_utc ,last_restored_file ,last_restored_date_utc ,last_restored_latency from msdb.dbo.log_shipping_monitor_secondary with (nolock) where not exists (select * from #log_shipping_monitor where upper(server) = upper(secondary_server) and database_name = secondary_database and is_primary = 0) order by primary_server, primary_database open #hcsecondaries fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id, @primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled, @last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency while (@@fetch_status != -1) begin -- -- Have we processed the primary for this secondary -- if not (upper(@primary_server) = upper(@prev_primary_server) and @primary_database = @prev_primary_database) begin -- -- No - Try to get the details of this primary -- select @primary_id = null if (upper(@secondary_server) = upper(@@servername)) begin -- -- we are on secondary -- get monitor server information -- select @monitor_server = monitor_server ,@monitor_server_security_mode = monitor_server_security_mode from msdb.dbo.log_shipping_secondary with (nolock) where secondary_id = @secondary_id select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end if (@is_monitor_local = 1) begin -- -- local monitor -- select @primary_id = primary_id ,@backup_threshold = backup_threshold ,@is_backup_alert_enabled = threshold_alert_enabled ,@last_backup_file = last_backup_file ,@last_backup_utc = last_backup_date_utc from msdb.dbo.log_shipping_monitor_primary with (nolock) where primary_server = upper(@primary_server) and primary_database = @primary_database end else begin -- -- remote monitor -- if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01)) begin -- -- execute as proxy -- exec @retcode = sys.sp_MSproxylogshippingmonitorhelpprimary @monitor_server = @monitor_server ,@p1 = @primary_server ,@p2 = @primary_database ,@p3 = @primary_id output ,@p4 = @backup_threshold output ,@p5 = @is_backup_alert_enabled output ,@p6 = @last_backup_file output ,@p7 = @last_backup_utc output end else begin delete #primary_monitor select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_primary ' ,@retcode = 0 begin try insert into #primary_monitor exec @retcode = @linkcmd @primary_server = @primary_server ,@primary_database = @primary_database end try begin catch select @retcode = 1 ,@primary_id = NULL end catch if (@retcode = 0) begin select @primary_id = primary_id ,@backup_threshold = backup_threshold ,@is_backup_alert_enabled = threshold_alert_enabled ,@last_backup_file = last_backup_file ,@last_backup_utc = last_backup_date_utc from #primary_monitor end else begin raiserror(32030, 10, 1, @primary_server, @primary_database, @monitor_server) end end -- processing remote end end -- processing on secondary else begin -- -- we are on monitor server -- select @primary_id = primary_id ,@backup_threshold = backup_threshold ,@is_backup_alert_enabled = threshold_alert_enabled ,@last_backup_file = last_backup_file ,@last_backup_utc = last_backup_date_utc from msdb.dbo.log_shipping_monitor_primary with (nolock) where primary_server = upper(@primary_server) and primary_database = @primary_database end -- processing on monitor server -- -- insert primary details if available -- if (@primary_id is not null) begin select @prev_primary_server = @primary_server ,@prev_primary_database = @primary_database ,@time_since_last_backup = datediff(minute, @last_backup_utc, @curutcdate) insert into #log_shipping_monitor ( status ,is_primary ,server ,database_name ,time_since_last_backup ,last_backup_file ,backup_threshold ,is_backup_alert_enabled) values ( case when ( @time_since_last_backup > @backup_threshold) then 1 else 0 end ,1 ,@primary_server ,@primary_database ,@time_since_last_backup ,@last_backup_file ,@backup_threshold ,@is_backup_alert_enabled) end -- primary data available end -- process the primary -- -- Insert the secondary -- select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate) ,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate) insert into #log_shipping_monitor ( status ,is_primary ,server ,database_name ,time_since_last_copy ,last_copied_file ,time_since_last_restore ,last_restored_file ,last_restored_latency ,restore_threshold ,is_restore_alert_enabled) values ( case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end ,0 ,@secondary_server ,@secondary_database ,@time_since_last_copy ,@last_copied_file ,@time_since_last_restore ,@last_restored_file ,@last_restored_latency ,@restore_threshold ,@is_restore_alert_enabled) -- -- get the next secondary -- fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id, @primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled, @last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency end -- while cursor for hcsecondaries close #hcsecondaries deallocate #hcsecondaries -- -- return resultset -- select status ,is_primary ,server ,database_name ,time_since_last_backup ,last_backup_file ,backup_threshold ,is_backup_alert_enabled ,time_since_last_copy ,last_copied_file ,time_since_last_restore ,last_restored_file ,last_restored_latency ,restore_threshold ,is_restore_alert_enabled from #log_shipping_monitor order by ts -- -- all done -- return 0 end
No comments:
Post a Comment