April 25, 2012

sp_help_log_shipping_monitor (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
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

Total Pageviews