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