Hi,
OS - SQL server 2008 R2
I am getting severe blocking during the day specially when there are ETL loads on the replicated databases.
There are 2 databases. Let's say <DBNAME> and <MINI_DBNAME>.
There are 2 replicates between them.
1 Transactions replication (certain tables)
1 Merge replications (certain tables)
There are no overlap of the tables above.
I am not sure why there are so many blocks like below and is there a way to over come..
I read http://www.g-productions.nl/index.php?name=sp_MSadd_distribution_history&version=2008RTM. it says about the SP been modified in SP1 and 2. Can I apply the new SP. Not sure if this has any impacts.
Any help is appreciated.
Captured Time : 10 Feb 2014 16:32:07:837
Blocking Session ID: 74
Waiting Session ID : 177
Database Name : distribution
Program Name : SQLAgent - TSQL JobStep (Job 0xB6927CB70F266E42A234FECBAC6CE97C : Step 1)
Login Name : <ADNAME>\administrator
Host Name : SERVERSQL01
Waiting Query : DELETE MSdistribution_history
WHERE agent_id = @agent_id
AND time <= @cutoff_time
AND timestamp not in (SELECT max(timestamp)
FROM MSdistribution_history
WHERE agent_id = @agent_id)
OPTION(MAXDOP 1)
Blocking Program : SQLAgent - TSQL JobStep (Job 0x851058945C93F94F86E159CC1B7A69D1 : Step 1)
Blocking Login : <ADNAME>\administrator
Blocking Host : SERVERSQL01
Blocking Query : CREATE PROCEDURE sp_MSsubscription_cleanup
@cutoff_time datetime
as
begin
set nocount on
declare @ACTIVE tinyint,
@INACTIVE tinyint,
@SUBSCRIBED tinyint,
@VIRTUAL smallint,
@SNAPSHOT_BIT int
declare @retcode int,
@max_time datetime,
@agent_id int,
@num_dropped int
declare @pub_db_id int,
@min_autonosync_lsn varbinary(16),
@new_autonosync_lsn varbinary(16),
@low_autonosync_lsn binary(8),
@high_autonosync_lsn binary(8),
@publication_id int
select @ACTIVE = 2,
@INACTIVE = 0,
@SUBSCRIBED = 1,
@VIRTUAL = -1,
@SNAPSHOT_BIT = 0x80000000
select @max_time = dateadd(hour, 1, getdate())
-- Refer to sp_MSmaximun_cleanup_xact_seqno to understand the logic
-- in this sp. If you change the logic here, you may need to change
-- that sp as well.
-- Deactivate real subscriptions for agents that are working on
-- transactions that are older than @retention
-- update all the subscriptions for those agents, including
-- subscriptions that are in subscribed state!
update MSsubscriptions
set status = @INACTIVE
where agent_id in (
select derivedInfo.agent_id
from (
-- Here we are retrieving the agent id, publisher database id,
-- min subscription sequence number, and the transaction seqno
-- related to the max timestamp row in the history table. this is
-- important since the tran seqno can go back to lower values in
-- the case of reinit with immediate sync.
select s.agent_id as agent_id,
s.publisher_database_id as publisher_database_id,
min(s.subscription_seqno) as subscription_seqno,
isnull(h.xact_seqno, 0x0) as xact_seqno
from MSsubscriptions s
left join (MSdistribution_history h with (REPEATABLEREAD)
join (select agent_id,
max(timestamp) as timestamp
from MSdistribution_history with (REPEATABLEREAD)
group by agent_id) as h2
on h.agent_id = h2.agent_id
and h.timestamp = h2.timestamp)
on s.agent_id = h.agent_id
where s.status = @ACTIVE
and s.subscriber_id >= 0 -- Only well-known agent
group by s.agent_id, -- agent and pubdbid as a pair can never be differnt
s.publisher_database_id,
isnull(h.xact_seqno, 0x0) -- because of join above we can include this
) derivedInfo
where @cutoff_time >= (
-- get the entry_time of the first transaction that cannot be
-- cleaned up normally because of this agent.
-- use history if it exists and is larger
case when derivedInfo.xact_seqno >= derivedInfo.subscription_seqno
then
-- join with commands table to filter out transactions that do not have commands
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- history xact_seqno can be cleaned up
and t.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
and c.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
order by t.xact_seqno asc), @max_time)
else
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- sub xact_seqno cannot be cleaned up
and t.xact_seqno >= derivedInfo.subscription_seqno
and c.xact_seqno >= derivedInfo.subscription_seqno
order by t.xact_seqno asc), @max_time)
end))
if @@rowcount <> 0
RAISERROR(21011, 10, -1)
-- Dropping all the aonymous agents that are working on
-- transactions that are older than @retention
-- No message raised.
-- Don't drop agents that do not have history (true for new agents).
-- For each publisher/publisherdb pair do cleanup
declare hC CURSOR LOCAL FAST_FORWARD FOR
select distinct derivedInfo.agent_id
from (
-- Here we are retrieving the agent id, publisher database id,
-- min subscription sequence number, and the transaction seqno
-- related to the max timestamp row in the history table. this is
-- important since the tran seqno can go back to lower values in
-- the case of reinit with immediate sync.
select msda.id as agent_id,
msda.publisher_database_id as publisher_database_id,
min(s.subscription_seqno) as subscription_seqno,
h.xact_seqno as xact_seqno
from MSsubscriptions s
join MSdistribution_agents msda
on s.agent_id = msda.virtual_agent_id
join (MSdistribution_history h with (REPEATABLEREAD)
join (select agent_id,
max(timestamp) as timestamp
from MSdistribution_history with (REPEATABLEREAD)
group by agent_id) as h2
on h.agent_id = h2.agent_id
and h.timestamp = h2.timestamp)
on msda.id = h.agent_id
where s.status = @ACTIVE
group by msda.id, -- agent and pubdbid as a pair can never be differnt
msda.publisher_database_id,
h.xact_seqno
) derivedInfo
where @cutoff_time >= (
-- Get the entry_time of the first tran that cannot be
-- cleaned up normally because of this agent.
-- use history if it exists and is larger
case when derivedInfo.xact_seqno > 0x00
then
-- does not have commands will not be picked up by sp_MSget_repl_commands
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- history xact_seqno can be cleaned up
and t.xact_seqno > derivedInfo.xact_seqno
and c.xact_seqno > derivedInfo.xact_seqno
order by t.xact_seqno asc), @max_time)
else
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- sub xact_seqno cannot be cleaned up
and t.xact_seqno >= isnull(derivedInfo.subscription_seqno, 0x0)
and c.xact_seqno >= isnull(derivedInfo.subscription_seqno, 0x0)
order by t.xact_seqno asc), @max_time)
end)
for read only
select @num_dropped = 0
open hC
fetch hC into @agent_id
while (@@fetch_status <> -1)
begin
exec @retcode = sys.sp_MSdrop_distribution_agentid_dbowner_proxy @agent_id
if @retcode <> 0 or @@error <> 0
return (1)
select @num_dropped = @num_dropped + 1
fetch hC into @agent_id
end
if @num_dropped > 0
RAISERROR(20597, 10, -1, @num_dropped)
-- Deactivating virtual subscriptions that are older then @retention.
update MSsubscriptions
set status = @SUBSCRIBED
-- Only change active subscriptions!
where status = @ACTIVE
and subscriber_id = @VIRTUAL
-- Get the entry_time of the first tran that cannot be
-- cleaned up normally because of this subscription.
and @cutoff_time >= isnull((select top 1 entry_time
from MSrepl_transactions t
where t.publisher_database_id = MSsubscriptions.publisher_database_id
and xact_seqno >= MSsubscriptions.subscription_seqno
order by t.xact_seqno asc), @max_time)
if @@rowcount <> 0
RAISERROR(21077, 10, -1)
-- Clear the min_noautosync_lsn value in MSpublications, if it specifies a time older than the retention period
-- This only applies to publications which are allowed for init from backup when there are no subscribers present.
-- We first find all publications enabled for init from backup with a min_autonosync_lsn specified
declare #pubC CURSOR FOR
select msp.publication_id, mspd.id, msp.min_autonosync_lsn from dbo.MSpublications msp
join dbo.MSpublisher_databases mspd on msp.publisher_id = mspd.publisher_id
and msp.publisher_db = mspd.publisher_db
where msp.allow_initialize_from_backup <> 0
and msp.min_autonosync_lsn is not null
and not exists(
select publisher_id from MSsubscriptions mss where
publisher_database_id = mspd.id)
for update of msp.publication_id
open #pubC
fetch next from #pubC into @publication_id, @pub_db_id, @min_autonosync_lsn
while (@@fetch_status <> -1)
begin
select @new_autonosync_lsn = null
-- Find the largest xact_seqno, that's outside of the retention period
select top 1 @new_autonosync_lsn = xact_seqno from dbo.MSrepl_transactions
where publisher_database_id = @pub_db_id
and xact_seqno >= @min_autonosync_lsn
and entry_time <= @cutoff_time
order by xact_seqno desc
if @new_autonosync_lsn is not null
begin
-- We have the largest xact_seqno that's outside of the retention period
-- however, this lsn is itself outside of the retention period, so we increment
-- the LSN by 1 in order to make sure it gets cleaned up properly
select @low_autonosync_lsn = substring(@new_autonosync_lsn, 9, 8)
select @high_autonosync_lsn = substring(@new_autonosync_lsn, 1, 8)
select @low_autonosync_lsn = cast(@low_autonosync_lsn as bigint) + 1
-- Check for overflow
if cast(@low_autonosync_lsn as bigint) = 0
select @high_autonosync_lsn = cast(@high_autonosync_lsn as bigint) + 1
-- Concat the two parts of the LSN
select @new_autonosync_lsn = @high_autonosync_lsn + @low_autonosync_lsn
-- update the autonosync_lsn to reflect the earliest command we can keep within the
-- retention period
update dbo.MSpublications
set min_autonosync_lsn = @new_autonosync_lsn
where publication_id = @publication_id
end
fetch next from #pubC into @publication_id, @pub_db_id, @min_autonosync_lsn
end
close #pubC
deallocate #pubC
return 0
end
Object Name :
Waiting Since : 392 seconds
----------------------------------------------------------
Captured Time : 10 Feb 2014 16:32:07:837
Blocking Session ID: 74
Waiting Session ID : 179
Database Name : distribution
Program Name : Replication Distribution History
Login Name : <ADNAME>\administrator
Host Name : SERVERSQL01
Waiting Query : 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)) )
Blocking Program : SQLAgent - TSQL JobStep (Job 0x851058945C93F94F86E159CC1B7A69D1 : Step 1)
Blocking Login : <ADNAME>\administrator
Blocking Host : SERVERSQL01
Blocking Query : CREATE PROCEDURE sp_MSsubscription_cleanup
@cutoff_time datetime
as
begin
set nocount on
declare @ACTIVE tinyint,
@INACTIVE tinyint,
@SUBSCRIBED tinyint,
@VIRTUAL smallint,
@SNAPSHOT_BIT int
declare @retcode int,
@max_time datetime,
@agent_id int,
@num_dropped int
declare @pub_db_id int,
@min_autonosync_lsn varbinary(16),
@new_autonosync_lsn varbinary(16),
@low_autonosync_lsn binary(8),
@high_autonosync_lsn binary(8),
@publication_id int
select @ACTIVE = 2,
@INACTIVE = 0,
@SUBSCRIBED = 1,
@VIRTUAL = -1,
@SNAPSHOT_BIT = 0x80000000
select @max_time = dateadd(hour, 1, getdate())
-- Refer to sp_MSmaximun_cleanup_xact_seqno to understand the logic
-- in this sp. If you change the logic here, you may need to change
-- that sp as well.
-- Deactivate real subscriptions for agents that are working on
-- transactions that are older than @retention
-- update all the subscriptions for those agents, including
-- subscriptions that are in subscribed state!
update MSsubscriptions
set status = @INACTIVE
where agent_id in (
select derivedInfo.agent_id
from (
-- Here we are retrieving the agent id, publisher database id,
-- min subscription sequence number, and the transaction seqno
-- related to the max timestamp row in the history table. this is
-- important since the tran seqno can go back to lower values in
-- the case of reinit with immediate sync.
select s.agent_id as agent_id,
s.publisher_database_id as publisher_database_id,
min(s.subscription_seqno) as subscription_seqno,
isnull(h.xact_seqno, 0x0) as xact_seqno
from MSsubscriptions s
left join (MSdistribution_history h with (REPEATABLEREAD)
join (select agent_id,
max(timestamp) as timestamp
from MSdistribution_history with (REPEATABLEREAD)
group by agent_id) as h2
on h.agent_id = h2.agent_id
and h.timestamp = h2.timestamp)
on s.agent_id = h.agent_id
where s.status = @ACTIVE
and s.subscriber_id >= 0 -- Only well-known agent
group by s.agent_id, -- agent and pubdbid as a pair can never be differnt
s.publisher_database_id,
isnull(h.xact_seqno, 0x0) -- because of join above we can include this
) derivedInfo
where @cutoff_time >= (
-- get the entry_time of the first transaction that cannot be
-- cleaned up normally because of this agent.
-- use history if it exists and is larger
case when derivedInfo.xact_seqno >= derivedInfo.subscription_seqno
then
-- join with commands table to filter out transactions that do not have commands
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- history xact_seqno can be cleaned up
and t.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
and c.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
order by t.xact_seqno asc), @max_time)
else
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- sub xact_seqno cannot be cleaned up
and t.xact_seqno >= derivedInfo.subscription_seqno
and c.xact_seqno >= derivedInfo.subscription_seqno
order by t.xact_seqno asc), @max_time)
end))
if @@rowcount <> 0
RAISERROR(21011, 10, -1)
-- Dropping all the aonymous agents that are working on
-- transactions that are older than @retention
-- No message raised.
-- Don't drop agents that do not have history (true for new agents).
-- For each publisher/publisherdb pair do cleanup
declare hC CURSOR LOCAL FAST_FORWARD FOR
select distinct derivedInfo.agent_id
from (
-- Here we are retrieving the agent id, publisher database id,
-- min subscription sequence number, and the transaction seqno
-- related to the max timestamp row in the history table. this is
-- important since the tran seqno can go back to lower values in
-- the case of reinit with immediate sync.
select msda.id as agent_id,
msda.publisher_database_id as publisher_database_id,
min(s.subscription_seqno) as subscription_seqno,
h.xact_seqno as xact_seqno
from MSsubscriptions s
join MSdistribution_agents msda
on s.agent_id = msda.virtual_agent_id
join (MSdistribution_history h with (REPEATABLEREAD)
join (select agent_id,
max(timestamp) as timestamp
from MSdistribution_history with (REPEATABLEREAD)
group by agent_id) as h2
on h.agent_id = h2.agent_id
and h.timestamp = h2.timestamp)
on msda.id = h.agent_id
where s.status = @ACTIVE
group by msda.id, -- agent and pubdbid as a pair can never be differnt
msda.publisher_database_id,
h.xact_seqno
) derivedInfo
where @cutoff_time >= (
-- Get the entry_time of the first tran that cannot be
-- cleaned up normally because of this agent.
-- use history if it exists and is larger
case when derivedInfo.xact_seqno > 0x00
then
-- does not have commands will not be picked up by sp_MSget_repl_commands
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- history xact_seqno can be cleaned up
and t.xact_seqno > derivedInfo.xact_seqno
and c.xact_seqno > derivedInfo.xact_seqno
order by t.xact_seqno asc), @max_time)
else
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- sub xact_seqno cannot be cleaned up
and t.xact_seqno >= isnull(derivedInfo.subscription_seqno, 0x0)
and c.xact_seqno >= isnull(derivedInfo.subscription_seqno, 0x0)
order by t.xact_seqno asc), @max_time)
end)
for read only
select @num_dropped = 0
open hC
fetch hC into @agent_id
while (@@fetch_status <> -1)
begin
exec @retcode = sys.sp_MSdrop_distribution_agentid_dbowner_proxy @agent_id
if @retcode <> 0 or @@error <> 0
return (1)
select @num_dropped = @num_dropped + 1
fetch hC into @agent_id
end
if @num_dropped > 0
RAISERROR(20597, 10, -1, @num_dropped)
-- Deactivating virtual subscriptions that are older then @retention.
update MSsubscriptions
set status = @SUBSCRIBED
-- Only change active subscriptions!
where status = @ACTIVE
and subscriber_id = @VIRTUAL
-- Get the entry_time of the first tran that cannot be
-- cleaned up normally because of this subscription.
and @cutoff_time >= isnull((select top 1 entry_time
from MSrepl_transactions t
where t.publisher_database_id = MSsubscriptions.publisher_database_id
and xact_seqno >= MSsubscriptions.subscription_seqno
order by t.xact_seqno asc), @max_time)
if @@rowcount <> 0
RAISERROR(21077, 10, -1)
-- Clear the min_noautosync_lsn value in MSpublications, if it specifies a time older than the retention period
-- This only applies to publications which are allowed for init from backup when there are no subscribers present.
-- We first find all publications enabled for init from backup with a min_autonosync_lsn specified
declare #pubC CURSOR FOR
select msp.publication_id, mspd.id, msp.min_autonosync_lsn from dbo.MSpublications msp
join dbo.MSpublisher_databases mspd on msp.publisher_id = mspd.publisher_id
and msp.publisher_db = mspd.publisher_db
where msp.allow_initialize_from_backup <> 0
and msp.min_autonosync_lsn is not null
and not exists(
select publisher_id from MSsubscriptions mss where
publisher_database_id = mspd.id)
for update of msp.publication_id
open #pubC
fetch next from #pubC into @publication_id, @pub_db_id, @min_autonosync_lsn
while (@@fetch_status <> -1)
begin
select @new_autonosync_lsn = null
-- Find the largest xact_seqno, that's outside of the retention period
select top 1 @new_autonosync_lsn = xact_seqno from dbo.MSrepl_transactions
where publisher_database_id = @pub_db_id
and xact_seqno >= @min_autonosync_lsn
and entry_time <= @cutoff_time
order by xact_seqno desc
if @new_autonosync_lsn is not null
begin
-- We have the largest xact_seqno that's outside of the retention period
-- however, this lsn is itself outside of the retention period, so we increment
-- the LSN by 1 in order to make sure it gets cleaned up properly
select @low_autonosync_lsn = substring(@new_autonosync_lsn, 9, 8)
select @high_autonosync_lsn = substring(@new_autonosync_lsn, 1, 8)
select @low_autonosync_lsn = cast(@low_autonosync_lsn as bigint) + 1
-- Check for overflow
if cast(@low_autonosync_lsn as bigint) = 0
select @high_autonosync_lsn = cast(@high_autonosync_lsn as bigint) + 1
-- Concat the two parts of the LSN
select @new_autonosync_lsn = @high_autonosync_lsn + @low_autonosync_lsn
-- update the autonosync_lsn to reflect the earliest command we can keep within the
-- retention period
update dbo.MSpublications
set min_autonosync_lsn = @new_autonosync_lsn
where publication_id = @publication_id
end
fetch next from #pubC into @publication_id, @pub_db_id, @min_autonosync_lsn
end
close #pubC
deallocate #pubC
return 0
end
Object Name :
Waiting Since : 392 seconds
----------------------------------------------------------
Captured Time : 10 Feb 2014 16:32:07:837
Blocking Session ID: 74
Waiting Session ID : 121
Database Name : distribution
Program Name : Replication Distribution History
Login Name : <ADNAME>\administrator
Host Name : SERVERSQL01
Waiting Query : 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)) )
Blocking Program : SQLAgent - TSQL JobStep (Job 0x851058945C93F94F86E159CC1B7A69D1 : Step 1)
Blocking Login : <ADNAME>\administrator
Blocking Host : SERVERSQL01
Blocking Query : CREATE PROCEDURE sp_MSsubscription_cleanup
@cutoff_time datetime
as
begin
set nocount on
declare @ACTIVE tinyint,
@INACTIVE tinyint,
@SUBSCRIBED tinyint,
@VIRTUAL smallint,
@SNAPSHOT_BIT int
declare @retcode int,
@max_time datetime,
@agent_id int,
@num_dropped int
declare @pub_db_id int,
@min_autonosync_lsn varbinary(16),
@new_autonosync_lsn varbinary(16),
@low_autonosync_lsn binary(8),
@high_autonosync_lsn binary(8),
@publication_id int
select @ACTIVE = 2,
@INACTIVE = 0,
@SUBSCRIBED = 1,
@VIRTUAL = -1,
@SNAPSHOT_BIT = 0x80000000
select @max_time = dateadd(hour, 1, getdate())
-- Refer to sp_MSmaximun_cleanup_xact_seqno to understand the logic
-- in this sp. If you change the logic here, you may need to change
-- that sp as well.
-- Deactivate real subscriptions for agents that are working on
-- transactions that are older than @retention
-- update all the subscriptions for those agents, including
-- subscriptions that are in subscribed state!
update MSsubscriptions
set status = @INACTIVE
where agent_id in (
select derivedInfo.agent_id
from (
-- Here we are retrieving the agent id, publisher database id,
-- min subscription sequence number, and the transaction seqno
-- related to the max timestamp row in the history table. this is
-- important since the tran seqno can go back to lower values in
-- the case of reinit with immediate sync.
select s.agent_id as agent_id,
s.publisher_database_id as publisher_database_id,
min(s.subscription_seqno) as subscription_seqno,
isnull(h.xact_seqno, 0x0) as xact_seqno
from MSsubscriptions s
left join (MSdistribution_history h with (REPEATABLEREAD)
join (select agent_id,
max(timestamp) as timestamp
from MSdistribution_history with (REPEATABLEREAD)
group by agent_id) as h2
on h.agent_id = h2.agent_id
and h.timestamp = h2.timestamp)
on s.agent_id = h.agent_id
where s.status = @ACTIVE
and s.subscriber_id >= 0 -- Only well-known agent
group by s.agent_id, -- agent and pubdbid as a pair can never be differnt
s.publisher_database_id,
isnull(h.xact_seqno, 0x0) -- because of join above we can include this
) derivedInfo
where @cutoff_time >= (
-- get the entry_time of the first transaction that cannot be
-- cleaned up normally because of this agent.
-- use history if it exists and is larger
case when derivedInfo.xact_seqno >= derivedInfo.subscription_seqno
then
-- join with commands table to filter out transactions that do not have commands
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- history xact_seqno can be cleaned up
and t.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
and c.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
order by t.xact_seqno asc), @max_time)
else
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- sub xact_seqno cannot be cleaned up
and t.xact_seqno >= derivedInfo.subscription_seqno
and c.xact_seqno >= derivedInfo.subscription_seqno
order by t.xact_seqno asc), @max_time)
end))
if @@rowcount <> 0
RAISERROR(21011, 10, -1)
-- Dropping all the aonymous agents that are working on
-- transactions that are older than @retention
-- No message raised.
-- Don't drop agents that do not have history (true for new agents).
-- For each publisher/publisherdb pair do cleanup
declare hC CURSOR LOCAL FAST_FORWARD FOR
select distinct derivedInfo.agent_id
from (
-- Here we are retrieving the agent id, publisher database id,
-- min subscription sequence number, and the transaction seqno
-- related to the max timestamp row in the history table. this is
-- important since the tran seqno can go back to lower values in
-- the case of reinit with immediate sync.
select msda.id as agent_id,
msda.publisher_database_id as publisher_database_id,
min(s.subscription_seqno) as subscription_seqno,
h.xact_seqno as xact_seqno
from MSsubscriptions s
join MSdistribution_agents msda
on s.agent_id = msda.virtual_agent_id
join (MSdistribution_history h with (REPEATABLEREAD)
join (select agent_id,
max(timestamp) as timestamp
from MSdistribution_history with (REPEATABLEREAD)
group by agent_id) as h2
on h.agent_id = h2.agent_id
and h.timestamp = h2.timestamp)
on msda.id = h.agent_id
where s.status = @ACTIVE
group by msda.id, -- agent and pubdbid as a pair can never be differnt
msda.publisher_database_id,
h.xact_seqno
) derivedInfo
where @cutoff_time >= (
-- Get the entry_time of the first tran that cannot be
-- cleaned up normally because of this agent.
-- use history if it exists and is larger
case when derivedInfo.xact_seqno > 0x00
then
-- does not have commands will not be picked up by sp_MSget_repl_commands
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- history xact_seqno can be cleaned up
and t.xact_seqno > derivedInfo.xact_seqno
and c.xact_seqno > derivedInfo.xact_seqno
order by t.xact_seqno asc), @max_time)
else
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- sub xact_seqno cannot be cleaned up
and t.xact_seqno >= isnull(derivedInfo.subscription_seqno, 0x0)
and c.xact_seqno >= isnull(derivedInfo.subscription_seqno, 0x0)
order by t.xact_seqno asc), @max_time)
end)
for read only
select @num_dropped = 0
open hC
fetch hC into @agent_id
while (@@fetch_status <> -1)
begin
exec @retcode = sys.sp_MSdrop_distribution_agentid_dbowner_proxy @agent_id
if @retcode <> 0 or @@error <> 0
return (1)
select @num_dropped = @num_dropped + 1
fetch hC into @agent_id
end
if @num_dropped > 0
RAISERROR(20597, 10, -1, @num_dropped)
-- Deactivating virtual subscriptions that are older then @retention.
update MSsubscriptions
set status = @SUBSCRIBED
-- Only change active subscriptions!
where status = @ACTIVE
and subscriber_id = @VIRTUAL
-- Get the entry_time of the first tran that cannot be
-- cleaned up normally because of this subscription.
and @cutoff_time >= isnull((select top 1 entry_time
from MSrepl_transactions t
where t.publisher_database_id = MSsubscriptions.publisher_database_id
and xact_seqno >= MSsubscriptions.subscription_seqno
order by t.xact_seqno asc), @max_time)
if @@rowcount <> 0
RAISERROR(21077, 10, -1)
-- Clear the min_noautosync_lsn value in MSpublications, if it specifies a time older than the retention period
-- This only applies to publications which are allowed for init from backup when there are no subscribers present.
-- We first find all publications enabled for init from backup with a min_autonosync_lsn specified
declare #pubC CURSOR FOR
select msp.publication_id, mspd.id, msp.min_autonosync_lsn from dbo.MSpublications msp
join dbo.MSpublisher_databases mspd on msp.publisher_id = mspd.publisher_id
and msp.publisher_db = mspd.publisher_db
where msp.allow_initialize_from_backup <> 0
and msp.min_autonosync_lsn is not null
and not exists(
select publisher_id from MSsubscriptions mss where
publisher_database_id = mspd.id)
for update of msp.publication_id
open #pubC
fetch next from #pubC into @publication_id, @pub_db_id, @min_autonosync_lsn
while (@@fetch_status <> -1)
begin
select @new_autonosync_lsn = null
-- Find the largest xact_seqno, that's outside of the retention period
select top 1 @new_autonosync_lsn = xact_seqno from dbo.MSrepl_transactions
where publisher_database_id = @pub_db_id
and xact_seqno >= @min_autonosync_lsn
and entry_time <= @cutoff_time
order by xact_seqno desc
if @new_autonosync_lsn is not null
begin
-- We have the largest xact_seqno that's outside of the retention period
-- however, this lsn is itself outside of the retention period, so we increment
-- the LSN by 1 in order to make sure it gets cleaned up properly
select @low_autonosync_lsn = substring(@new_autonosync_lsn, 9, 8)
select @high_autonosync_lsn = substring(@new_autonosync_lsn, 1, 8)
select @low_autonosync_lsn = cast(@low_autonosync_lsn as bigint) + 1
-- Check for overflow
if cast(@low_autonosync_lsn as bigint) = 0
select @high_autonosync_lsn = cast(@high_autonosync_lsn as bigint) + 1
-- Concat the two parts of the LSN
select @new_autonosync_lsn = @high_autonosync_lsn + @low_autonosync_lsn
-- update the autonosync_lsn to reflect the earliest command we can keep within the
-- retention period
update dbo.MSpublications
set min_autonosync_lsn = @new_autonosync_lsn
where publication_id = @publication_id
end
fetch next from #pubC into @publication_id, @pub_db_id, @min_autonosync_lsn
end
close #pubC
deallocate #pubC
return 0
end
Object Name :
Waiting Since : 396 seconds
----------------------------------------------------------
Captured Time : 10 Feb 2014 16:32:07:837
Blocking Session ID: 121
Waiting Session ID : 155
Database Name : distribution
Program Name : SQLAgent - TSQL JobStep (Job 0xCE6F6C076D95C1439718E94E780E65FD : Step 1)
Login Name : <ADNAME>\administrator
Host Name : SERVERSQL01
Waiting Query : 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).
*/
Blocking Program : Replication Distribution History
Blocking Login : <ADNAME>\administrator
Blocking Host : SERVERSQL01
Blocking Query : 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 int
,@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 int
,@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 = 2147483647
-- 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
Object Name :
Waiting Since : 127 seconds
----------------------------------------------------------
Captured Time : 10 Feb 2014 16:32:07:837
Blocking Session ID: 175
Waiting Session ID : 74
Database Name : distribution
Program Name : SQLAgent - TSQL JobStep (Job 0x851058945C93F94F86E159CC1B7A69D1 : Step 1)
Login Name : <ADNAME>\administrator
Host Name : SERVERSQL01
Waiting Query : update MSsubscriptions
set status = @INACTIVE
where agent_id in (
select derivedInfo.agent_id
from (
-- Here we are retrieving the agent id, publisher database id,
-- min subscription sequence number, and the transaction seqno
-- related to the max timestamp row in the history table. this is
-- important since the tran seqno can go back to lower values in
-- the case of reinit with immediate sync.
select s.agent_id as agent_id,
s.publisher_database_id as publisher_database_id,
min(s.subscription_seqno) as subscription_seqno,
isnull(h.xact_seqno, 0x0) as xact_seqno
from MSsubscriptions s
left join (MSdistribution_history h with (REPEATABLEREAD)
join (select agent_id,
max(timestamp) as timestamp
from MSdistribution_history with (REPEATABLEREAD)
group by agent_id) as h2
on h.agent_id = h2.agent_id
and h.timestamp = h2.timestamp)
on s.agent_id = h.agent_id
where s.status = @ACTIVE
and s.subscriber_id >= 0 -- Only well-known agent
group by s.agent_id, -- agent and pubdbid as a pair can never be differnt
s.publisher_database_id,
isnull(h.xact_seqno, 0x0) -- because of join above we can include this
) derivedInfo
where @cutoff_time >= (
-- get the entry_time of the first transaction that cannot be
-- cleaned up normally because of this agent.
-- use history if it exists and is larger
case when derivedInfo.xact_seqno >= derivedInfo.subscription_seqno
then
-- join with commands table to filter out transactions that do not have commands
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- history xact_seqno can be cleaned up
and t.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
and c.xact_seqno > isnull( derivedInfo.xact_seqno, 0x0 )
order by t.xact_seqno asc), @max_time)
else
isnull((select top 1 entry_time
from MSrepl_transactions t,
MSrepl_commands c,
MSsubscriptions sss
where sss.agent_id = derivedInfo.agent_id
and t.publisher_database_id = derivedInfo.publisher_database_id
and c.publisher_database_id = derivedInfo.publisher_database_id
and c.xact_seqno = t.xact_seqno
-- filter out snapshot transactions not for this subscription
-- because they do not represent significant data changes
and ((c.type & @SNAPSHOT_BIT ) <> @SNAPSHOT_BIT
or (c.xact_seqno >= sss.subscription_seqno
and c.xact_seqno <= sss.ss_cplt_seqno))
-- filter out non-subscription articles for independent agents
and c.article_id = sss.article_id
-- sub xact_seqno cannot be cleaned up
and t.xact_seqno >= derivedInfo.subscription_seqno
and c.xact_seqno >= derivedInfo.subscription_seqno
order by t.xact_seqno asc), @max_time)
end))
Blocking Program : Repl-LogReader-0-<DBNAME>-19
Blocking Login : <ADNAME>\administrator
Blocking Host : SERVERSQL01
Blocking Query : CREATE PROCEDURE sp_MSadd_replcmds
@publisher_database_id int,
@publisher_id smallint,
@publisher_db sysname,
@data varbinary(1595),
@1data varbinary(1595) = NULL,
@2data varbinary(1595) = NULL,
@3data varbinary(1595) = NULL,
@4data varbinary(1595) = NULL,
@5data varbinary(1595) = NULL,
@6data varbinary(1595) = NULL,
@7data varbinary(1595) = NULL,
@8data varbinary(1595) = NULL,
@9data varbinary(1595) = NULL,
@10data varbinary(1595) = NULL,
@11data varbinary(1595) = NULL,
@12data varbinary(1595) = NULL,
@13data varbinary(1595) = NULL,
@14data varbinary(1595) = NULL,
@15data varbinary(1595) = NULL,
@16data varbinary(1595) = NULL,
@17data varbinary(1595) = NULL,
@18data varbinary(1595) = NULL,
@19data varbinary(1595) = NULL,
@20data varbinary(1595) = NULL,
@21data varbinary(1595) = NULL,
@22data varbinary(1595) = NULL,
@23data varbinary(1595) = NULL,
@24data varbinary(1595) = NULL,
@25data varbinary(1595) = NULL,
@26data varbinary(1595) = NULL
AS
BEGIN
SET NOCOUNT ON
DECLARE @date datetime
,@x int
,@tempdata varbinary(1595)
DECLARE @xactId varbinary(10),
@xactSeqNo varbinary(10),
@artId int,
@cmdId int,
@cmdType int,
@fIncomplete bit,
@cmdLen int,
@originator_id int,
@origSrvLen int,
@origDbLen int,
@origPublId int,
@origDbVersion int,
@origLSN varbinary(10),
@hashKey int,
@cmdText varbinary(1595),
@originator sysname,
@originatorDb sysname
SELECT @date = GETDATE()
select @x = 0
select @tempdata = null
while @x <= 26
begin
select @tempdata = CASE @x
when 0 then @data
when 1 then @1data
when 2 then @2data
when 3 then @3data
when 4 then @4data
when 5 then @5data
when 6 then @6data
when 7 then @7data
when 8 then @8data
when 9 then @9data
when 10 then @10data
when 11 then @11data
when 12 then @12data
when 13 then @13data
when 14 then @14data
when 15 then @15data
when 16 then @16data
when 17 then @17data
when 18 then @18data
when 19 then @19data
when 20 then @20data
when 21 then @21data
when 22 then @22data
when 23 then @23data
when 24 then @24data
when 25 then @25data
when 26 then @26data
end
if @tempdata is NULL
goto END_CMDS
-- We will now breakup the binary data. Check HP_FIXED_DATA
-- in publish.cpp for all of the offsets listed below...
select @xactId = substring( @tempdata, 1, 10),
@xactSeqNo = substring( @tempdata, 11, 10),
@artId = substring( @tempdata, 21, 4),
@cmdId = substring( @tempdata, 25, 4),
@cmdType = substring( @tempdata, 29, 4),
@fIncomplete = convert(bit, substring( @tempdata, 33, 1)),
@cmdLen = substring( @tempdata, 34, 2),
@origSrvLen = substring( @tempdata, 36, 2),
@origDbLen = substring( @tempdata, 38, 2),
@hashKey = substring( @tempdata, 40, 2),
-- @origPublId = only done below if an originator len is detected : usually = substring( @tempdata, 42, 4)
-- @origDbVersion=only done below if an originator len is detected : usually = substring( @tempdata, 46, 4)
@origLSN = substring( @tempdata, 50, 10),
@cmdText = substring( @tempdata, 60, @cmdLen)
-- @originator = only done below if an originator len is detected : usually = substring( @tempdata, 60 + @cmdLen, @origSrvLen)
-- @originatorDb= only done below if an originator len is detected : usually = substring( @tempdata, 60 + @cmdLen + @origSrvLen, @origDbLen)
IF @cmdId = 1
begin
INSERT INTO MSrepl_transactions
VALUES (@publisher_database_id, @xactId, @xactSeqNo, @date)
end
-- do special processing for the different command typs if needed
if( @cmdType in( 37,38 ) )
begin
select @cmdType = 38 - @cmdType
exec sp_MSset_syncstate @publisher_id, @publisher_db, @artId, @cmdType, @xactSeqNo
select @cmdType = (38 - @cmdType) | 0x80000000
end
-- Check all posted cmds of SQLCMD type to see if they are tracer records
-- sql cmd type is (47 | 0x40000000) or 1073741871
else if @cmdType = 1073741871
begin
declare @tracer_id int,
@retcode int
select @tracer_id = cast(cast(@cmdText as nvarchar) as int)
exec @retcode = sys.sp_MSupdate_tracer_history @tracer_id = @tracer_id
if @retcode <> 0 or @@error <> 0
return 1
end
-- only add it if the command is not empty
if @cmdLen > 0
begin
-- Get the originator_id for the first command
if @origSrvLen <> 0 and @origDbLen <> 0
begin
select @originator_id = null,
@originator = substring( @tempdata, 60 + @cmdLen, @origSrvLen),
@originatorDb = substring( @tempdata, 60 + @cmdLen + @origSrvLen, @origDbLen),
@origPublId = substring( @tempdata, 42, 4),
@origDbVersion = substring( @tempdata, 46, 4)
-- if @origPublId and @origDbVersion is 0 or NULL
-- then we are not in Peer-To-Peer so we do not need
-- to set the dbversion and publication id values...
if isnull(@origPublId, 0) != 0
and isnull(@origDbVersion, 0) != 0
begin
select @originator_id = id
from MSrepl_originators with (readpast)
where publisher_database_id = @publisher_database_id
and UPPER(srvname) = UPPER(@originator)
and dbname = @originatorDb
and publication_id = @origPublId
and dbversion = @origDbVersion
end
else
begin
select @origPublId = NULL,
@origDbVersion = NULL
select @originator_id = id
from MSrepl_originators
where publisher_database_id = @publisher_database_id
and UPPER(srvname) = UPPER(@originator)
and dbname = @originatorDb
and publication_id is NULL
and dbversion is NULL
end
if @originator_id is null
begin
insert into MSrepl_originators (publisher_database_id, srvname, dbname, publication_id, dbversion)
values (@publisher_database_id, @originator, @originatorDb, @origPublId, @origDbVersion)
select @originator_id = @@identity
end
end
else
select @originator_id = 0
INSERT INTO MSrepl_commands
(
publisher_database_id,
xact_seqno,
type,
article_id,
originator_id,
command_id,
partial_command,
hashkey,
originator_lsn,
command
)
VALUES
(
@publisher_database_id,
@xactSeqNo,
@cmdType,
@artId,
@originator_id,
@cmdId,
@fIncomplete,
@hashKey,
@origLSN,
@cmdText
)
end
select @x = @x + 1
end
END_CMDS:
IF @@ERROR <> 0
return (1)
END
Object Name : DF__IHpublica__allow__693CA210
Waiting Since : 2226 seconds
Blockings during replication
Posted - 2014-02-11 : 10:53:38