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