May 2, 2012

sp_MSadd_distribution_history (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_distribution_history(int @agent_id
, int @runstatus
, nvarchar @comments
, binary @xact_seqno
, int @delivered_transactions
, int @delivered_commands
, float @delivery_rate
, bit @log_error
, bit @perfmon_increment
, varbinary @xactseq
, int @command_id
, bit @update_existing_row
, bit @updateable_row
, bit @do_raiserror)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_distribution_history   
(
@agent_id int,
@runstatus int,
@comments nvarchar(max),
@xact_seqno binary(16) = 0x00, -- We use binary(16)to pad it out for the below compare
@delivered_transactions int = 0, -- Running total for the session
@delivered_commands int = 0, -- Running total for the session
@delivery_rate float = 0, -- Last rate (cmds/sec)
@log_error bit = 0,
@perfmon_increment bit = 1,
@xactseq varbinary(16) = NULL,
@command_id int = NULL,
@update_existing_row bit = 0,
@updateable_row bit = 1, -- used to override history verbose level to decide
-- whether the row being added can be updated by another.
@do_raiserror bit = 1
)
AS
BEGIN

set nocount on

DECLARE @current_time datetime
,@start_time datetime
,@entry_time datetime
,@duration int -- milliseconds
,@delivery_latency int
,@average_commands int
,@total_cmds bigint
,@publisher_id smallint
,@publisher_db sysname
,@publication sysname
,@publisher sysname
,@subscriber_id smallint
,@subscriber sysname
,@subscriber_db sysname
,@article sysname
,@article_id int
,@publication_id int
,@publisher_database_id int

,@agent_name nvarchar(100)
,@error_id int
,@startup int
,@succeed int
,@inprogress int
,@retry int
,@failure int
,@validation_failure int
,@validation_success int, @error_skipped int
,@requested_shutdown int
,@raiserror_status int
,@idle int
,@lastrow_timestamp timestamp
,@lastrow_xact_seqno binary(16)
,@new_delivered_commands int
,@new_delivered_transactions int
,@retcode int
,@last_delivery_rate float
,@last_delivery_latency int
,@avg_delivery_rate float
,@avg_delivery_latency int
,@perfmon_delivery_rate int
,@existing_row_updateble bit
,@this_row_updateable bit
,@agentclassname sysname
,@MAXINT bigint
,@prev_runstatus int
,@prev_start_time datetime

--
-- PAL Security Check
--
exec @retcode = sys.sp_MScheck_pull_access
@agent_id = @agent_id,
@agent_type = 0 -- distribution agent
if @@error <> 0 or @retcode <> 0
return (1)
--
-- Status const defined in sqlrepl.h
--
select @startup = 1
,@succeed = 2
,@inprogress = 3
,@idle = 4
,@retry = 5
,@failure = 6
,@validation_failure = 7
,@validation_success = 8
,@requested_shutdown = 9
,@error_skipped = 10
,@MAXINT = 9223372036854775807

-- To prevent cleanup up being messed up by invalid history message, only log
-- valid history message.
if @runstatus > 10 or @runstatus < 0
begin
-- Invalid history message logged
RAISERROR (21079, 16, -1, @runstatus)
return (1)
end

if(@update_existing_row = 1 and substring(@comments, 1, 7) = N'<stats ')
set @update_existing_row = 0

select @existing_row_updateble = 1
select @this_row_updateable = 1

select @raiserror_status = @runstatus
if (@runstatus = @validation_failure or @runstatus = @validation_success
or @runstatus = @requested_shutdown or @runstatus = @error_skipped)
begin
select @runstatus = @inprogress
select @this_row_updateable = 0
end

if (@updateable_row = 0)
begin
select @this_row_updateable = 0
end

SELECT @current_time = GETDATE()

-- Update Perfmon counter
if @perfmon_increment = 1
begin
if @runstatus = @startup
dbcc incrementinstance ("SQL Replication Agents", "Running", "Distribution", 1)
else if (@runstatus = @succeed or @runstatus = @retry or @runstatus = @failure)
dbcc incrementinstance ("SQL Replication Agents", "Running", "Distribution", -1)
end

-- Get agent name, publisher id and publisher_db
select @agent_name = name,
@publisher_database_id = publisher_database_id,
@publisher_id = publisher_id, @publisher_db = publisher_db,
@publication = publication, @subscriber_id = subscriber_id, @subscriber_db = subscriber_db
from MSdistribution_agents
where id = @agent_id
select @publisher = srvname from master.dbo.sysservers where srvid = @publisher_id
select @subscriber = srvname from master.dbo.sysservers where srvid = @subscriber_id

-- Get start_time and xact_seqno for latest agent run --
IF @runstatus <> 1
BEGIN

SELECT TOP 1
@lastrow_xact_seqno = xact_seqno,
@start_time = start_time,
@total_cmds = total_delivered_commands,
@lastrow_timestamp = timestamp,
@new_delivered_transactions = @delivered_transactions - delivered_transactions,
@new_delivered_commands = @delivered_commands - delivered_commands,
@last_delivery_rate = delivery_rate,
@last_delivery_latency = delivery_latency,
@existing_row_updateble = updateable_row
FROM MSdistribution_history with (rowlock)
WHERE agent_id = @agent_id
and comments not like N'<stats state%'
ORDER BY timestamp DESC

--
-- Check the case where the user did not pass in the proper values
-- for delivered commands and transactions (this leads to negative
-- new command/tran counts).
--
if ( @new_delivered_commands < 0 )
SELECT @new_delivered_commands = 0

if ( @new_delivered_transactions < 0 )
SELECT @new_delivered_transactions = 0
END
ELSE
BEGIN
-- At least get running total of commands over all sessions.
SELECT TOP 1
@prev_runstatus = runstatus,
@prev_start_time = start_time,
@lastrow_xact_seqno = xact_seqno,
@total_cmds = total_delivered_commands,
@last_delivery_latency = delivery_latency
FROM MSdistribution_history with (rowlock)
WHERE agent_id = @agent_id
ORDER BY timestamp DESC

set @last_delivery_latency = isnull(@last_delivery_latency, 0)
-- Set Startup Perfmon counters
dbcc addinstance ("SQL Replication Distribution", @agent_name)
dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, @last_delivery_latency)

IF @prev_runstatus IN (@startup, @retry)
BEGIN
SELECT @start_time = @prev_start_time
END
ELSE
BEGIN
SELECT @start_time = @current_time
END
SELECT @new_delivered_commands = @delivered_commands
SELECT @new_delivered_transactions = @delivered_transactions
SELECT @last_delivery_rate = 0
SELECT @last_delivery_latency = 0
END

IF @total_cmds IS NULL
SELECT @total_cmds = 0

if @new_delivered_commands IS NULL
SELECT @new_delivered_commands = 0

if @new_delivered_transactions IS NULL
SELECT @new_delivered_transactions = 0

-- Use the current time if no corresponding start_up message logged --
IF @start_time is NULL
SELECT @start_time = @current_time

-- Calculate agent run duration --
SELECT @duration = DATEDIFF(second, @start_time, @current_time)

IF @delivered_commands <> 0 and @delivered_transactions <> 0
SELECT @average_commands = @delivered_commands/@delivered_transactions
ELSE
SELECT @average_commands = 0

-- Get the entry time of the last distributed transaction
if @xact_seqno <> 0x00 and @new_delivered_commands <> 0
-- SELECT @entry_time = entry_time FROM MSrepl_transactions with (READPAST)
SELECT @entry_time = entry_time FROM MSrepl_transactions with (nolock)
WHERE xact_seqno = @xact_seqno and
publisher_database_id = @publisher_database_id

-- Calculate the latency of the last distributed transaction
IF @entry_time IS NOT NULL
begin
-- Calculte diff in minutes.
declare @diff_min int
select @diff_min = DATEDIFF(minute, @entry_time, @current_time)
if @diff_min > 16666
select @delivery_latency = 999999999
else
select @delivery_latency = DATEDIFF(millisecond, @entry_time, @current_time)
end
ELSE
SELECT @delivery_latency = 0

-- Calculate the average delivery latency of the session
IF @last_delivery_latency = 0 or @last_delivery_latency is null
SET @avg_delivery_latency = @delivery_latency
ELSE IF @delivery_latency = 0
SET @avg_delivery_latency = @last_delivery_latency
ELSE
SET @avg_delivery_latency = (@delivery_latency + @last_delivery_latency)/2

-- at the end of snapshot, set delivery_latency to 0 so that monitor does not raise false alarm
if @runstatus = @succeed
begin
if substring(@comments, 1, 20) = N'Applied the snapshot'
begin
SELECT @delivery_latency = 0, @avg_delivery_latency = 0
end
end

-- Calculate average delivery rate of the session
IF @last_delivery_rate = 0 or @last_delivery_rate is null
SET @avg_delivery_rate = @delivery_rate
ELSE IF @delivery_rate = 0 or @new_delivered_commands = 0
SET @avg_delivery_rate = @last_delivery_rate
ELSE
SET @avg_delivery_rate = (@delivery_rate + @last_delivery_rate)/2.0

-- Calculate grand total of delivered trans across sessions, check
-- to make sure the result does not overflow integer column
--
if (@total_cmds > @MAXINT - @new_delivered_commands)
SET @total_cmds = @MAXINT
else
SET @total_cmds = @total_cmds + @new_delivered_commands

-- Set Perfmon counters
-- Note that Startup perfmon counters are set above
-- now that we may write in-prograss msg with 0 cmds 0 trans for informational purpose
-- , no need to write those to perfmon
if @runstatus = @idle or (@runstatus = @inprogress and @new_delivered_transactions > 0 and @new_delivered_commands > 0)
begin
dbcc addinstance ("SQL Replication Distribution", @agent_name)
dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Trans/sec", @agent_name, @new_delivered_transactions)
dbcc incrementinstance ("SQL Replication Distribution", "Dist:Delivered Cmds/sec", @agent_name, @new_delivered_commands)
dbcc setinstance ("SQL Replication Distribution", "Dist:Delivery Latency", @agent_name, @delivery_latency)
end

--
-- Set error id to 0 unless the user want to log errors associate with this
-- history message.
--
SELECT @error_id = 0
IF @log_error = 1
-- Ignore errors here. @error_id will be set to 0 in case of errors
EXEC sys.sp_MSget_new_errorid @error_id OUTPUT, @xactseq, @command_id

-- @xact_seqno may be uninitialized for the first several messages after
-- the start-up of the distribtion agent. Get the correct value in that case.
-- We must do this because distribution cleanup will use the lastest xact_seqno
-- as cleanup boundary.
-- Note: @last_xact_seqno might be NULL
-- Only do this if @xact_seqno is 0, since a smaller xact_seqno might be logged due
-- to reinited sub for immediate_sync pub.
-- This will prevent history being messed up by one gabage history entry.
if @xact_seqno = 0x00 and @lastrow_xact_seqno is not null
select @xact_seqno = @lastrow_xact_seqno

-- Insert idle record or update if history record is already 'idle'
IF (@existing_row_updateble = 1) and (@runstatus = @idle or @update_existing_row = 1)
begin

-- Attempt to update the last row if it is IDLE
if (@runstatus = @idle)
begin
UPDATE MSdistribution_history SET runstatus = @runstatus, time = @current_time,
duration = @duration, comments = @comments,
xact_seqno = @xact_seqno, updateable_row = @this_row_updateable,
error_id = case @error_id when 0 then error_id else @error_id end
WHERE
agent_id = @agent_id and
timestamp = @lastrow_timestamp and
( runstatus = @runstatus or
(@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) )
end
else
begin
-- Attempt to update the last row if it is IDLE
UPDATE MSdistribution_history SET runstatus = @runstatus, start_time = @start_time,
time = @current_time,
duration = @duration,
xact_seqno = @xact_seqno,
comments = @comments,
delivered_transactions = @delivered_transactions,
delivered_commands = @delivered_commands,
average_commands = @average_commands,
delivery_rate = @avg_delivery_rate,
delivery_latency = @avg_delivery_latency,
total_delivered_commands = @total_cmds,
current_delivery_rate = @delivery_rate,
current_delivery_latency = @delivery_latency,
updateable_row = @this_row_updateable,
error_id = case @error_id when 0 then error_id else @error_id end
WHERE
agent_id = @agent_id and
timestamp = @lastrow_timestamp and
( runstatus = @runstatus or
(@update_existing_row = 1 and runstatus in (@idle, @inprogress) and @runstatus in (@idle, @inprogress)) )
end

-- Insert idle record if there is not one
if @@ROWCOUNT = 0
begin
INSERT INTO MSdistribution_history (agent_id, runstatus, start_time, time, duration, comments, xact_seqno,
delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency,
total_delivered_commands, error_id, timestamp, current_delivery_rate, current_delivery_latency, updateable_row)
VALUES (@agent_id, @runstatus, @start_time, @current_time,
@duration, @comments, @xact_seqno, @delivered_transactions,
@delivered_commands, @average_commands, @avg_delivery_rate,
@avg_delivery_latency, @total_cmds, @error_id, NULL, @delivery_rate, @delivery_latency, @this_row_updateable)
end
end
else
begin
INSERT INTO MSdistribution_history (agent_id, runstatus, start_time, time, duration, comments, xact_seqno,
delivered_transactions, delivered_commands, average_commands, delivery_rate, delivery_latency,
total_delivered_commands, error_id, timestamp, current_delivery_rate, current_delivery_latency, updateable_row)
VALUES (@agent_id, @runstatus, @start_time, @current_time,
@duration, @comments, @xact_seqno, @delivered_transactions,
@delivered_commands, @average_commands, @avg_delivery_rate,
@avg_delivery_latency, @total_cmds, @error_id, NULL, @delivery_rate, @delivery_latency, @this_row_updateable)
end

if (@raiserror_status = @validation_failure or @raiserror_status = @validation_success or
@raiserror_status = @error_skipped)
begin
-- Get the "real" publication name (as opposed to 'ALL') and article name
select @article_id = article_id from MSrepl_commands with (nolock)
where publisher_database_id = @publisher_database_id
and xact_seqno = @xactseq
and command_id = @command_id

select @publication = mp.publication, @publication_id = mp.publication_id
from dbo.MSpublications as mp, dbo.MSsubscriptions as ms
where mp.publisher_id = ms.publisher_id
and mp.publisher_db = ms.publisher_db
and mp.publication_id = ms.publication_id
and ms.publisher_id = @publisher_id
and ms.publisher_db = @publisher_db
and ms.subscriber_id = @subscriber_id
and ms.subscriber_db = @subscriber_db
and ms.article_id = @article_id

select @article = article
from MSarticles
where article_id = @article_id
and publisher_id = @publisher_id
and publisher_db = @publisher_db
and publication_id = @publication_id
end

-- Raise the appropriate error
if @do_raiserror = 1
begin
select @agentclassname = formatmessage(14553)

-- only use the first 255 chars
select @comments = left(@comments, 255)

exec sys.sp_MSrepl_raiserror @agentclassname, @agent_name, @raiserror_status, @comments, @subscriber=@subscriber, @publication=@publication, @article=@article
end

IF @@ERROR <> 0
RETURN (1)
END

No comments:

Post a Comment

Total Pageviews