May 2, 2012

sp_MSadd_log_shipping_history_detail (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_MSadd_log_shipping_history_detail(uniqueidentifier @agent_id
, tinyint @agent_type
, int @session_id
, tinyint @session_status
, nvarchar @database
, nvarchar @last_processed_file_name
, nvarchar @message)

MetaData:

 create procedure sys.sp_MSadd_log_shipping_history_detail   
(
@agent_id uniqueidentifier -- primary/secondary ID
,@agent_type tinyint -- 0 = Backup, 1 = Copy, 2 = Restore
,@session_id int
,@session_status tinyint -- 0 = Starting, 1 = Running, 2 = Success, 3 = Error, 4 = Warning
,@database sysname = NULL -- primary/secondary database
,@last_processed_file_name nvarchar(500) = NULL
,@message nvarchar(4000) = N'No message specified'
)
as
begin
set nocount on
declare @retcode int
,@agent_idstring sysname
,@curdate datetime
,@curutcdate datetime
,@last_restored_latency int
,@monitor_server sysname
,@monitor_server_security_mode int
,@errlogstr nvarchar(4000)
,@agentjobid uniqueidentifier
,@fileindex int
,@loopindex int

--
-- 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
--
-- validate agent_type
--
if (@agent_type not in (0,1,2))
begin
raiserror(21055, 16, -1, '@agent_type','sp_MSadd_log_shipping_history_detail')
return 1
end
--
-- validate session_status
--
if (@session_status not in (0,1,2,3,4))
begin
raiserror(21055, 16, -1, '@session_status','sp_MSadd_log_shipping_history_detail')
return 1
end
--
-- validate agent
--
if (sys.fn_MSvalidatelogshipagentid(@agent_id, @agent_type) = 0)
begin
select @agent_idstring = cast(@agent_id as sysname)
raiserror(32016, 16, 1, @agent_idstring, @agent_type)
return 1
end
--
-- set the dates
--
select @curdate = getdate()
,@curutcdate = getutcdate()
--
-- get monitor server information
--
if (@agent_type = 0)
begin
select @monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
,@agentjobid = backup_job_id
from msdb.dbo.log_shipping_primary_databases
where primary_id = @agent_id
end
else
begin
select @monitor_server = monitor_server
,@monitor_server_security_mode = monitor_server_security_mode
from msdb.dbo.log_shipping_secondary
where secondary_id = @agent_id
end
--
-- add history record for monitoring
--
exec @retcode = sys.sp_MSprocesslogshippingmonitorhistory
@mode = 1
,@agent_id = @agent_id
,@agent_type = @agent_type
,@session_id = @session_id
,@session_status = @session_status
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@database = @database
,@log_time = @curdate
,@log_time_utc = @curutcdate
,@message = @message
if (@retcode != 0 or @@error != 0)
goto UNDO
--
-- process @last_processed_file_name
--
if (@last_processed_file_name is not null)
begin
--
-- process based on agent_type
--
if (@agent_type = 0)
begin
--
-- backup agent
--
-- add errorlog message for rename
--
select @backuplognoext = substring(@last_processed_file_name, 1, charindex(N'.trn', @last_processed_file_name) - 1)
select @agentjobname = name
from msdb.dbo.sysjobs_view
where job_id = @agentjobid
and category_id = 6
if exists (select * from master.dbo.sysmessages where error = 32029)
begin
raiserror(32029, 10, 1, @agentjobname, @backuplognoext, @backuplognoext) with log
end
else
begin
raiserror(N'Log shipping backup agent [%s] has verified log backup file ''%s.wrk'' and renamed it as ''%s.trn''.', 10, 1, @agentjobname, @backuplognoext, @backuplognoext) with log
end
--
--
-- update primary
--
update msdb.dbo.log_shipping_primary_databases
set last_backup_file = @last_processed_file_name
,last_backup_date = @curdate
where primary_id = @agent_id
if (@@error != 0)
goto UNDO
--
-- update primary monitor metadata
--
exec @retcode = sp_MSprocesslogshippingmonitorprimary @mode = 3
,@primary_id = @agent_id
,@primary_server = @@servername
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@primary_database = @database
,@last_backup_file = @last_processed_file_name
,@last_backup_date = @curdate
,@last_backup_date_utc = @curutcdate
if (@retcode != 0 or @@error != 0)
goto UNDO
end
else if (@agent_type = 1)
begin
--
-- copy agent
--
update msdb.dbo.log_shipping_secondary
set last_copied_file = @last_processed_file_name
,last_copied_date = @curdate
where secondary_id = @agent_id
if (@@error != 0)
goto UNDO
--
-- update secondary monitor metadata
--
exec @retcode = sp_MSprocesslogshippingmonitorsecondary @mode = 3
,@secondary_server = @@servername
,@secondary_database = @database
,@secondary_id = @agent_id
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@last_copied_file = @last_processed_file_name
,@last_copied_date = @curdate
,@last_copied_date_utc = @curutcdate
if (@retcode != 0 or @@error != 0)
goto UNDO
end
else
begin
--
-- restore agent
--
update msdb.dbo.log_shipping_secondary_databases
set last_restored_file = @last_processed_file_name
,last_restored_date = @curdate
where secondary_id = @agent_id
and (@database is null or secondary_database = @database)
if (@@error != 0)
goto UNDO
--
-- compute last_restored_latency
-- we have to find the last '_' from @last_processed_file_name
-- to initiate the timestamp processing
--
-- since @last_processed_file_name is a file path
-- it may contain directories with '_' embedded in the path
--
select @fileindex = 0
while (1=1)
begin
select @loopindex = charindex(N'_', substring(@last_processed_file_name, @fileindex + 1, 500))
if (@loopindex > 0)
select @fileindex = @fileindex + @loopindex
else
break;
end
--
-- The time format in the filename should always be in the
-- following format:yyyyMMddHHmmss (the LS agent code
-- uses this format explicitly) - when processing the string
-- containing time from file, append milliseconds and use
-- CONVERT style 21 to match the format:yyyy-mm-dd hh:mi:ss.mmm
--
select @last_restored_latency = datediff(minute
,convert(datetime, stuff(stuff(stuff(stuff(stuff(substring(@last_processed_file_name, @fileindex + 1, 14), 5, 0, N'-'), 8, 0, N'-'), 11, 0, N' '), 14, 0, N':'), 17, 0, N':') + N'.000' , 21)
,@curutcdate)
--
-- update secondary monitor metadata
--
exec @retcode = sp_MSprocesslogshippingmonitorsecondary @mode = 3
,@secondary_server = @@servername
,@secondary_database = @database
,@secondary_id = @agent_id
,@monitor_server = @monitor_server
,@monitor_server_security_mode = @monitor_server_security_mode
,@last_restored_file = @last_processed_file_name
,@last_restored_date = @curdate
,@last_restored_date_utc = @curutcdate
,@last_restored_latency = @last_restored_latency
if (@retcode != 0 or @@error != 0)
goto UNDO
end
end
--
-- all done
--
return 0

UNDO:
return 1
end

No comments:

Post a Comment

Total Pageviews