May 15, 2012

sp_MSget_repl_commands (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_MSget_repl_commands(int @agent_id
, varbinary @last_xact_seqno
, tinyint @get_count
, int @compatibility_level
, int @subdb_version
, int @read_query_size)

MetaData:

 CREATE PROCEDURE sys.sp_MSget_repl_commands  
(
@agent_id int,
@last_xact_seqno varbinary(16),
@get_count tinyint = 0, -- 0 = no count, 1 = cmd and tran (legacy), 2 = cmd only
@compatibility_level int = 7000000,
@subdb_version int = 0,
@read_query_size int = -1
)
as
begin
set nocount on

declare @active_status tinyint
,@initiate_status tinyint
,@snapshot_bit int
,@postcmd_bit int
,@synctran_type int
,@read_only tinyint
,@retcode int
,@publisher_database_id int
,@originator_id int
,@subscriber sysname
,@subscriber_db sysname
,@subscriber_id smallint
,@publisher sysname
,@publisher_db sysname
,@publication sysname
,@publisher_id smallint
,@max_xact_seqno varbinary(16)
,@concurrent int
,@concurrent_c int
,@IsP2PAgent bit
,@undelivered_commands int
,@undelivered_transactions int

declare @peer_originator_ids table(id int primary key)

select @read_only = 0
,@active_status = 2
,@initiate_status = 3
,@snapshot_bit = 0x80000000
,@postcmd_bit = 0x40000000
,@concurrent = 3
,@concurrent_c = 4
,@undelivered_commands = 0
,@undelivered_transactions = 0
select @synctran_type = @snapshot_bit | 9

-- Security Check
-- @agent_id might be null when it comes from sp_MSget_repl_cmd_anonymous
if @agent_id is not null
begin
exec @retcode = sys.sp_MScheck_pull_access
@agent_id = @agent_id,
@agent_type = 0 -- distribution agent
if @@error <> 0 or @retcode <> 0
return (1)
end
else
begin
return (1)
end
-- validate @get_count
if @get_count not in (0,1,2)
return (1)
-- Get publisher database id etc.
SELECT @publisher_database_id = publisher_database_id,
@publisher_db = publisher_db,
@publisher_id = publisher_id,
@subscriber_id = subscriber_id,
@subscriber_db = subscriber_db,
@publication = publication,
@publisher = srvname
from MSdistribution_agents
inner join master.dbo.sysservers
on master.dbo.sysservers.srvid = publisher_id
where id = @agent_id

-- Find out if there are any PeerToPeer publications for this agent
-- If so, we will generate extra "originator" columns in the resultset
exec sys.sp_MSispeertopeeragent @agent_id = @agent_id, @is_p2p = @IsP2PAgent output

-- Get the last xact_seqno on the pub db FIRST. It will
-- be used as the upper bound for differnt queries. We have to do
-- this to prevent transactions on new or changed subscriptions or
-- with new orignator_id being skipped eigher by preselected query or
-- preselected originator_id.
-- Have to have readpast here to prevent the query be blocked by logreader
-- (even before the first row to the dist agent).
--
-- Note: DO NOT consider distcmds inserted for a concurrent snapshot
-- These represent 'forward in time' xacts. Skipping past these xacts
-- can cause lost transactions!
--
-- Note2: consider adding new MSrepl_commands idx with keys:
-- publisher_database_id, command_id, type, xact_seqno
--
-- Note3: might be able to use replpostcmd + a LSN mapping token to
-- allow SS agent to post sync cmds to log instead of dist db.
--
-- Note4 : The max_xact_seqno is calculated based on the @read_query_size parameter - this parameter
-- limit the number of commands retrieved by this call.
if(@read_query_size <= 0)
begin
select @max_xact_seqno = max(xact_seqno) from MSrepl_commands with (READPAST)
where
publisher_database_id = @publisher_database_id and
command_id = 1 and
type <> -2147483611
end
else
begin
-- calculate the @max_xact_seqno from the next @read_query_size commands.
declare @GetMaxCommand nvarchar(1024)
select @GetMaxCommand = N'select top ' + convert(nvarchar(1024),@read_query_size)+
N' @max_xact_seqno = xact_seqno from MSrepl_commands with (READPAST)
where
publisher_database_id = @publisher_database_id and
type <> -2147483611 and
xact_seqno > @last_xact_seqno
order by xact_seqno, command_id asc'


exec sys.sp_executesql
@GetMaxCommand,
N'@max_xact_seqno varbinary(16) output ,@last_xact_seqno varbinary(16),@publisher_database_id int',
@publisher_database_id = @publisher_database_id,
@max_xact_seqno = @max_xact_seqno output,
@last_xact_seqno=@last_xact_seqno

if(@max_xact_seqno is null)
select @max_xact_seqno = @last_xact_seqno
end


-- If there's nothing to do, return here to avoid more queries.
if @max_xact_seqno = @last_xact_seqno
begin
if @get_count in (1,2)
begin
if (@get_count = 2)
begin
select N'undelivered_commands' = @undelivered_commands
end
else -- @get_count = 1
begin
select
N'undelivered_commands' = @undelivered_commands,
N'undelivered_transactions' = @undelivered_transactions
end
end
else -- @get_count = 0
begin
select rc.xact_seqno, rc.partial_command, rc.type,
rc.command_id, rc.command
from
MSrepl_commands rc
where 0 = 1
select @max_xact_seqno
end
return 0
end

-- Get subscriber name
select @subscriber = srvname from master.dbo.sysservers where
srvid = @subscriber_id

-- Note: if no originator id in the table, it will be 0, so that no loop back
-- detection will be done!.
-- Since the logreader will insert into the MSrepl_originators table,
-- this query has to be later then get max seqno query!!!!!
select @originator_id = 0

if @IsP2PAgent = 0
begin
select @originator_id = ISNULL(id, 0) from MSrepl_originators where
publisher_database_id = @publisher_database_id and
UPPER(srvname) = UPPER(@subscriber) and
dbname = @subscriber_db and
publication_id is NULL and
dbversion is NULL
end
else
begin
-- for the peer to peer case we need to make sure to retrieve the
-- originator id based on the dbversion.
--
-- keep in mind that if we don't find a row then the values in @peer_originator_ids
-- will be 0. This is ok since this can only occur if:
-- 1. No commands have been inserted into the distribution db for the
-- subscriber and we are delivering snapshot commands. This is ok
-- since we do not perform loop-back detection for snapshot commands.
-- or
-- 2. We just restored the subscriber and we are attempting to reapply
-- all missing commands. In this case the query to retrieve all commands
-- process correctly since the commands originating from the subscriber
-- must be applied and we know that no new commands should be generated
-- until all past commands have been delivered.
insert into @peer_originator_ids (id)
select distinct id
from MSrepl_originators
where publisher_database_id = @publisher_database_id
and UPPER(srvname) = UPPER(@subscriber)
and dbname = @subscriber_db
and dbversion = @subdb_version
and publication_id is not NULL

if not exists(select *
from @peer_originator_ids)
begin
insert into @peer_originator_ids(id) values(0)
end
end

if @get_count in (1,2)
begin
--
-- Performance impact
-- Issue the select for counts separately
-- Do first select for undelivered commands
--
select @undelivered_commands = count(*)
from MSrepl_commands rc WITH (NOLOCK)
JOIN dbo.MSsubscriptions s
ON (rc.article_id = s.article_id
AND rc.publisher_database_id=s.publisher_database_id )
where

s.agent_id = @agent_id and
s.status = @active_status and
rc.publisher_database_id = @publisher_database_id and
rc.xact_seqno > @last_xact_seqno and
rc.xact_seqno <= @max_xact_seqno and
-- If log based transaction, we do
-- 1. only select tran later than sub pub seqno
-- 2. loopback detection
(((rc.type & @snapshot_bit) <> @snapshot_bit and
rc.xact_seqno > s.publisher_seqno and
rc.xact_seqno > s.ss_cplt_seqno and
--
-- Loopback detection
--
(s.loopback_detection = 0
or (@originator_id != 0
and rc.originator_id <> @originator_id)
or @originator_id = 0)) or
-- If snapshot transaction, we do
-- 1. filter out the snapshot transactions that were inserted later that is not
-- the subscription's snapshot transaction
-- 2. filter out trigger generation command for non synctran subscription.
-- Note: don't do loop back detection.
((rc.type & @snapshot_bit) = @snapshot_bit and
rc.xact_seqno >= s.subscription_seqno and
rc.xact_seqno <= s.ss_cplt_seqno and
(s.update_mode <> @read_only or rc.type <> @synctran_type))) and
-- Filter out the new command types that we have introduced after 9.0
(@compatibility_level >= 10000000 or
(rc.type & ~@snapshot_bit & ~@postcmd_bit) not in
(105)) and
-- Filter out the new command types that we have introduced after 8.0
(@compatibility_level >= 9000000 or
(rc.type & ~@snapshot_bit & ~@postcmd_bit) not in
(13, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 31, 32, 33, 47, 102)) and
-- Filter out the new command types that we have introduced after 7.0
(@compatibility_level > 7000000 or
(rc.type & ~@snapshot_bit) not in
(25, 40, 45, 50, 51, 52, 53, 54, 55, 56 ,57, 58, 60, 68, 69))
--
-- process undelivered transaction only if @get_count = 1
--
if (@get_count = 2)
begin
select N'undelivered_commands' = @undelivered_commands
end
else
begin
--
-- Do second select for undelivered transactions
-- TBD - see if MSrepl_transactions can be used instead of MSrepl_commands
--
select @undelivered_transactions = count(distinct xact_seqno)
from MSrepl_commands rc WITH (NOLOCK)
JOIN dbo.MSsubscriptions s
ON (rc.article_id = s.article_id
AND rc.publisher_database_id=s.publisher_database_id )
where

s.agent_id = @agent_id and
s.status = @active_status and
rc.publisher_database_id = @publisher_database_id and
rc.xact_seqno > @last_xact_seqno and
rc.xact_seqno <= @max_xact_seqno and
-- If log based transaction, we do
-- 1. only select tran later than sub pub seqno
-- 2. loopback detection
(((rc.type & @snapshot_bit) <> @snapshot_bit and
rc.xact_seqno > s.publisher_seqno and
rc.xact_seqno > s.ss_cplt_seqno and
--
-- Loopback detection
--
(s.loopback_detection = 0
or (@originator_id != 0
and rc.originator_id <> @originator_id)
or @originator_id = 0)) or
-- If snapshot transaction, we do
-- 1. filter out the snapshot transactions that were inserted later that is not
-- the subscription's snapshot transaction
-- 2. filter out trigger generation command for non synctran subscription.
-- Note: don't do loop back detection.
((rc.type & @snapshot_bit) = @snapshot_bit and
rc.xact_seqno >= s.subscription_seqno and
rc.xact_seqno <= s.ss_cplt_seqno and
(s.update_mode <> @read_only or rc.type <> @synctran_type))) and
-- Filter out the new command types that we have introduced after 9.0
(@compatibility_level >= 10000000 or
(rc.type & ~@snapshot_bit & ~@postcmd_bit) not in
(105)) and
-- Filter out the new command types that we have introduced after 8.0
(@compatibility_level >= 9000000 or
(rc.type & ~@snapshot_bit & ~@postcmd_bit) not in
(13, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 31, 32, 33, 47, 102)) and
-- Filter out the new command types that we have introduced after 7.0
(@compatibility_level > 7000000 or
(rc.type & ~@snapshot_bit) not in
(25, 40, 45, 50, 51, 52, 53, 54, 55, 56 ,57, 58, 60, 68, 69))

select N'undelivered_commands' = @undelivered_commands,
N'undelivered_transactions' = @undelivered_transactions
end

return(0)
end

-- if this agent is servicing any inactive concurrent sync articles
-- then return no rows. The concurrent sync article will be activated
-- when the SYNCDONE token is written to the distribution db by the logreader
-- at this point, all log records associated with the sync will be present in
-- MSrepl_commands

if exists( SELECT * FROM dbo.MSsubscriptions s JOIN dbo.MSpublications p
ON( s.publisher_id = p.publisher_id and
s.publisher_db = p.publisher_db and
s.publication_id = p.publication_id )
WHERE
s.agent_id = @agent_id and
s.status = @initiate_status and
(p.sync_method = @concurrent or p.sync_method = @concurrent_c) )
begin
select rc.xact_seqno, rc.partial_command, rc.type,
rc.command_id, rc.command
from
MSrepl_commands rc
where 0 = 1
select @last_xact_seqno
return 0
end

-- Decide on a best query method.
-- Note: The order of the following queries is important and
-- not abitrary.

-- Get subscription info
declare @num_non_active int
declare @num_article int
declare @num_loopback int
declare @max_sub_seqno varbinary(16)
declare @max_pub_seqno varbinary(16)

select
@num_non_active = sum(case when status <> @active_status then 1 else 0 end),
@num_article = count(*),
@num_loopback = sum(case when loopback_detection <> 0 then 1 else 0 end),
@max_sub_seqno = max(subscription_seqno),
@max_pub_seqno = max(publisher_seqno)
from dbo.MSsubscriptions where
agent_id = @agent_id

if @last_xact_seqno < @max_sub_seqno or
@last_xact_seqno < @max_pub_seqno or
@num_non_active <> 0
-- The agent is still working on snapshot transactions. Need a full join in this case
begin

-- Need a work table to remember the set of snapshot transaction
-- sequence numbers that need to be applied by this agent, this
-- table of subscription sequence numbers can then be used to make
-- sure that snapshot control (header/trailer) associated with
-- incremental snapshot commands are enumerated
declare @snapshot_seqnos table (subscription_seqno varbinary(16) primary key)

insert into @snapshot_seqnos
select distinct subscription_seqno
from dbo.MSsubscriptions
where agent_id = @agent_id
and subscription_seqno > @last_xact_seqno

-- in case of @last_xact_seqno = 0x0 we can adjust it to the
-- min subscription_seqno of of all articles for this subscriptions minus one.
-- This will boost perf.
if (@last_xact_seqno = 0x0)
begin
select @last_xact_seqno = CONVERT(varbinary(16),min(subscription_seqno))
from @snapshot_seqnos

if(@last_xact_seqno = 0x0 or @last_xact_seqno is null)
select @last_xact_seqno = 0x0;
else -- dec 1
begin
declare @low as bigint
declare @high as bigint
select @high = substring(@last_xact_seqno,1,8)
select @low = substring(@last_xact_seqno,9,8)
if(@low <> 0)
select @low = @low - 1
else
begin
select @high=@high - 1
select @low = 0xFFFFFFFFFFFFFFFFFFFF
end

select @last_xact_seqno = convert(varbinary(16),@high) + convert(varbinary(16),@low)
end
end

-- no loopback and no PeerToPeer publications
if @originator_id = 0 and @IsP2PAgent = 0
begin
-- Join with every thing but no loop back
select rc.xact_seqno, rc.partial_command, rc.type,
rc.command_id, rc.command, rc.hashkey

from
MSrepl_commands rc with (INDEX(ucMSrepl_commands))
JOIN dbo.MSsubscriptions s with (INDEX(ucMSsubscriptions))
-- At end, we use the FASTFIRSTROW option which tends to force
-- a nested inner loop join driven from MSrepl_commands
ON (rc.article_id = s.article_id AND rc.publisher_database_id=s.publisher_database_id )
where

s.agent_id = @agent_id and
s.status = @active_status and
rc.publisher_database_id = @publisher_database_id and
rc.xact_seqno > @last_xact_seqno and
rc.xact_seqno <= @max_xact_seqno and
-- If log based transaction, we do
-- 1. only select tran later than sub pub seqno
-- 2. only select tran later than ss_cplt_seqno
(((rc.type & @snapshot_bit) <> @snapshot_bit and
rc.xact_seqno > s.publisher_seqno and
rc.xact_seqno > s.ss_cplt_seqno )
or
-- If snapshot transaction, we do
-- 1. filter out the snapshot transactions that were inserted later that is not
-- the subscription's snapshot transaction
-- 2. filter out trigger generation command for non synctran subscription.
-- Note: don't do loop back detection.
((rc.type & @snapshot_bit) = @snapshot_bit and
((rc.xact_seqno >= s.subscription_seqno and
rc.xact_seqno <= s.ss_cplt_seqno) or
(rc.xact_seqno in (select subscription_seqno from @snapshot_seqnos) and (rc.type & ~@snapshot_bit) in (25, 50, 51, 52, 53, 54, 55, 56 ,57, 58))) and -- Command type list must match that in sp_MSdrop_article
(s.update_mode <> @read_only or rc.type <> @synctran_type))) and
-- Filter out the new command types that we have introduced after 9.0
(@compatibility_level >= 10000000 or
(rc.type & ~@snapshot_bit & ~@postcmd_bit) not in
(105)) and
-- Filter out the new command types that we have introduced after 8.0
(@compatibility_level >= 9000000 or
(rc.type & ~@snapshot_bit & ~@postcmd_bit) not in
(13, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 31, 32, 33, 47, 100, 101, 102, 104)) and
-- Filter out the new command types that we have introduced after 7.0
(@compatibility_level > 7000000 or
(rc.type & ~@snapshot_bit) not in
(25, 40, 45, 50, 51, 52, 53, 54, 55, 56 ,57, 58, 60, 68, 69))
order by rc.xact_seqno, rc.command_id asc
OPTION (FAST 1)
end
-- PeerToPeer QUERY (with Snapshot Commands)
else if @IsP2PAgent = 1
begin
select rc.xact_seqno, rc.partial_command, rc.type,
rc.command_id, rc.command, rc.hashkey,
-- extra columns for the PeerToPeer resultset
msro.id, msro.srvname, msro.dbname, msro.publication_id, msro.dbversion, rc.originator_lsn
from
MSrepl_commands rc with (INDEX(ucMSrepl_commands))
join dbo.MSsubscriptions s with (INDEX(ucMSsubscriptions))
on rc.article_id = s.article_id
-- needed for PeerToPeer
left join MSrepl_originators msro with (readpast, INDEX(ucMSrepl_originators))
on msro.id = rc.originator_id
left join MScached_peer_lsns mscpl with (INDEX(ucMScached_peer_lsns))
on
(
mscpl.agent_id = s.agent_id
and mscpl.originator = msro.srvname
and mscpl.originator_db = msro.dbname
and mscpl.originator_publication_id = msro.publication_id
and mscpl.originator_db_version = msro.dbversion
)
where
s.agent_id = @agent_id and
s.status = @active_status and
s.publisher_database_id = @publisher_database_id and
rc.publisher_database_id = @publisher_database_id and
rc.xact_seqno > @last_xact_seqno and
rc.xact_seqno <= @max_xact_seqno and
-- If log based transaction, we do
-- 1. only select tran later than sub pub seqno
-- 2. perform loop back detection for P2P and
-- allow all 1073741868 cmds to be replicated
(((rc.type & @snapshot_bit) <> @snapshot_bit and
rc.xact_seqno > s.publisher_seqno and
rc.xact_seqno > s.ss_cplt_seqno
--
-- Loopback detection
--
-- We send the command for 4 cases:
--
-- 1. We have the originator information and the lsn of the cmd is
-- greater than the last one we saw for this originator.
--
-- 2. This is a snapshot command and the originator id is NULL. This
-- would mean that the command qualified via the left join and thus
-- the originator_lsn must also be NULL. Note that the only time an
-- originator_id is NULL in P2P should be for snapshot commands
--
-- 3. The originator_id of the command is not null, it is not the
-- subscriber originator_id (@originator_id) and the lsn is null. This
-- is the case where the meta-data has not yet been inserted at the sub.
--
-- 4.If is a REPL_PEERTOPEERAUTOPROC (1073741868) command type. They
-- are always sent since we guarantee they only come from one source.
-- also we must send these for the following case:
-- 2 Nodes A and B. DDL occurs on A and sends auto-proc to B
-- Now B must send the procs back to A as a refresh even though
-- the AutoProcs are marked as coming from A. The procs will
-- Not be resent by this node because of @last_xact_seqno. This
-- does mean however that it is possible that another node may
-- resend. This is ok since the same definition will be used
and ((not exists(select * from @peer_originator_ids where id = rc.originator_id)
and mscpl.originator_lsn < rc.originator_lsn)
or (rc.originator_id is NULL
and mscpl.originator_lsn is NULL)
or (not exists(select * from @peer_originator_ids where id = rc.originator_id)
and mscpl.originator_lsn is NULL)
or rc.type = 1073741868))
or
-- If snapshot transaction, we do
-- 1. filter out the snapshot transactions that were inserted later that is not
-- the subscription's snapshot transaction
-- 2. filter out trigger generation command for non synctran subscription.
-- Note: don't do loop back detection.
((rc.type & @snapshot_bit) = @snapshot_bit and
((rc.xact_seqno >= s.subscription_seqno and
rc.xact_seqno <= s.ss_cplt_seqno) or
(rc.xact_seqno in (select subscription_seqno from @snapshot_seqnos) and (rc.type & ~@snapshot_bit) in (25, 50, 51, 52, 53, 54, 55, 56 ,57, 58))) and -- Command type list must match that in sp_MSdrop_article
(s.update_mode <> @read_only or rc.type <> @synctran_type)))
-- here we do not require filtering out new commands since this should only
-- be reached by agents with compatibility levels that are 9.0 or greater
order by rc.xact_seqno, rc.command_id asc
OPTION (FAST 1)
end
else -- this is loop back detection (NO PeerToPeer)
begin
-- Join with every thing with loop back
select rc.xact_seqno, rc.partial_command, rc.type,
rc.command_id, rc.command, rc.hashkey
from
MSrepl_commands rc with (INDEX(ucMSrepl_commands))
JOIN dbo.MSsubscriptions s with (INDEX(ucMSsubscriptions))
-- At end, we use the FASTFIRSTROW option which tends to force
-- a nested inner loop join driven from MSrepl_commands
ON (rc.article_id = s.article_id AND rc.publisher_database_id=s.publisher_database_id )
where

s.agent_id = @agent_id and
s.status = @active_status and
rc.publisher_database_id = @publisher_database_id and
rc.xact_seqno > @last_xact_seqno and
rc.xact_seqno <= @max_xact_seqno and
-- If log based transaction, we do
-- 1. only select tran later than sub pub seqno
-- 2. loopback detection
(((rc.type & @snapshot_bit) <> @snapshot_bit and
rc.xact_seqno > s.publisher_seqno and
rc.xact_seqno > s.ss_cplt_seqno and
--
-- Loopback detection
--
(s.loopback_detection = 0 or
rc.originator_id <> @originator_id)) or
-- If snapshot transaction, we do
-- 1. filter out the snapshot transactions that were inserted later that is not
-- the subscription's snapshot transaction
-- 2. filter out trigger generation command for non synctran subscription.
-- Note: don't do loop back detection.
((rc.type & @snapshot_bit) = @snapshot_bit and
((rc.xact_seqno >= s.subscription_seqno and
rc.xact_seqno <= s.ss_cplt_seqno) or
(rc.xact_seqno in (select subscription_seqno from @snapshot_seqnos) and (rc.type & ~@snapshot_bit) in (25, 50, 51, 52, 53, 54, 55, 56 ,57, 58))) and -- Command type list must match that in sp_MSdrop_article
(s.update_mode <> @read_only or rc.type <> @synctran_type))) and
-- Filter out the new command types that we have introduced after 9.0
(@compatibility_level >= 10000000 or
(rc.type & ~@snapshot_bit & ~@postcmd_bit) not in
(105)) and
-- Filter out the new command types that we have introduced after 8.0
(@compatibility_level >= 9000000 or
(rc.type & ~@snapshot_bit & ~@postcmd_bit) not in
(13, 86, 87, 88, 89, 90, 91, 92, 93, 94, 95, 96, 97, 98, 99, 31, 32, 33, 47, 100, 101, 102, 104)) and
-- Filter out the new command types that we have introduced after 7.0
(@compatibility_level > 7000000 or
(rc.type & ~@snapshot_bit) not in
(25, 40, 45, 50, 51, 52, 53, 54, 55, 56 ,57, 58, 60, 68, 69))
order by rc.xact_seqno, rc.command_id asc
OPTION (FAST 1)
end
end
-- PeerToPeer QUERY (NO Snapshot Commands)
else if @IsP2PAgent = 1
begin
select rc.xact_seqno, rc.partial_command, rc.type,
rc.command_id, rc.command, rc.hashkey,
-- extra columns for the PeerToPeer resultset
msro.id, msro.srvname, msro.dbname, msro.publication_id, msro.dbversion, rc.originator_lsn
from
MSrepl_commands rc with (nolock, INDEX(ucMSrepl_commands))
join dbo.MSsubscriptions s with (INDEX(ucMSsubscriptions))
on rc.article_id = s.article_id
-- needed for PeerToPeer
left join MSrepl_originators msro with (readpast, INDEX(ucMSrepl_originators))
on msro.id = rc.originator_id
left join MScached_peer_lsns mscpl with (INDEX(ucMScached_peer_lsns))
on
(
mscpl.agent_id = s.agent_id
and mscpl.originator = msro.srvname
and mscpl.originator_db = msro.dbname
and mscpl.originator_publication_id = msro.publication_id
and mscpl.originator_db_version = msro.dbversion
)
where
s.agent_id = @agent_id and
s.publisher_database_id = @publisher_database_id and
rc.publisher_database_id = @publisher_database_id and
rc.xact_seqno > @last_xact_seqno and
rc.xact_seqno <= @max_xact_seqno and
(rc.type & @snapshot_bit) <> @snapshot_bit and
(rc.type & ~@snapshot_bit) not in ( 37, 38 )
--
-- Loopback detection
--
-- We send the command for 4 cases:
--
-- 1. We have the originator information and the lsn of the cmd is
-- greater than the last one we saw for this originator.
--
-- 2. This is a snapshot command and the originator id is NULL. This
-- would mean that the command qualified via the left join and thus
-- the originator_lsn must also be NULL. Note that the only time an
-- originator_id is NULL in P2P should be for snapshot commands
--
-- 3. The originator_id of the command is not null, it is not the
-- subscriber originator_id (@originator_id) and the lsn is null. This
-- is the case where the meta-data has not yet been inserted at the sub.
--
-- 4.If is a REPL_PEERTOPEERAUTOPROC (1073741868) command type. They
-- are always sent since we guarantee they only come from one source.
-- also we must send these for the following case:
-- 2 Nodes A and B. DDL occurs on A and sends auto-proc to B
-- Now B must send the procs back to A as a refresh even though
-- the AutoProcs are marked as coming from A. The procs will
-- Not be resent by this node because of @last_xact_seqno. This
-- does mean however that it is possible that another node may
-- resend. This is ok since the same definition will be used
and ((not exists(select * from @peer_originator_ids where id = rc.originator_id)
and mscpl.originator_lsn < rc.originator_lsn)
or (rc.originator_id is NULL
and mscpl.originator_lsn is NULL)
or (not exists(select * from @peer_originator_ids where id = rc.originator_id)
and mscpl.originator_lsn is NULL)
or rc.type = 1073741868)
-- here we do not require filtering out new commands since this should only
-- be reached by agents with compatibility levels that are 9.0 or greater
order by rc.xact_seqno, rc.command_id asc
OPTION (FAST 1)
end
-- The agent has finished snapshot transactions but it has loopback detection.
else if @num_loopback <> 0 and @originator_id <> 0
begin
-- Join plus loopback
select rc.xact_seqno, rc.partial_command, rc.type,
rc.command_id, rc.command, rc.hashkey
from
MSrepl_commands rc with (nolock, INDEX(ucMSrepl_commands))
JOIN dbo.MSsubscriptions s with (INDEX(ucMSsubscriptions))
-- At end, we use the FASTFIRSTROW option which tends to force
-- a nested inner loop join driven from MSrepl_commands
ON (rc.article_id = s.article_id)
where

s.agent_id = @agent_id and
rc.publisher_database_id = @publisher_database_id and
rc.xact_seqno > @last_xact_seqno and
rc.xact_seqno <= @max_xact_seqno and
(rc.type & @snapshot_bit) <> @snapshot_bit and
(rc.type & ~@snapshot_bit) not in ( 37, 38 ) and
--
-- Loopback detection
--
(s.loopback_detection = 0 or
rc.originator_id <> @originator_id)
and (@compatibility_level >= 9000000
or (rc.type & ~@postcmd_bit) not in (47))
order by rc.xact_seqno, rc.command_id asc
OPTION (FAST 1)
end
-- The agent has finished snapshot transactions. It has NO loopback detection.
else
begin
-- Mini join along. Only agent_id and article_id columns in dbo.MSsubscriptions
-- are used. So only index pages are needed for the join.
select rc.xact_seqno, rc.partial_command, rc.type,
rc.command_id, rc.command, rc.hashkey,
-- extra columns for the PeerToPeer resultset
-- originator_id, srvname, dbname, originator_publication_id, originator_db_version, originator_lsn
NULL, NULL, NULL, NULL, NULL, NULL
from
MSrepl_commands rc with (nolock, INDEX(ucMSrepl_commands))
JOIN dbo.MSsubscriptions s with (INDEX(ucMSsubscriptions))
-- At end, we use the FASTFIRSTROW option which tends to force
-- a nested inner loop join driven from MSrepl_commands
ON (rc.article_id = s.article_id)
where
s.agent_id = @agent_id and
rc.publisher_database_id = @publisher_database_id and
rc.xact_seqno > @last_xact_seqno and
rc.xact_seqno <= @max_xact_seqno and
(rc.type & @snapshot_bit) <> @snapshot_bit and
(rc.type & ~@snapshot_bit) not in ( 37, 38 )
and (@compatibility_level >= 9000000
or (rc.type & ~@postcmd_bit) not in (47))
order by rc.xact_seqno, rc.command_id asc
OPTION (FAST 1)
end

-- Return the max seqno of this batch to distribution agent.
select @max_xact_seqno, @@rowcount
end

No comments:

Post a Comment

Total Pageviews