Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_replmonitorrefreshagentdata

  No additional text.


Syntax
create procedure sys.sp_replmonitorrefreshagentdata
as
begin
    set nocount on
    declare @retcode int
                ,@agent_id int
                ,@agent_id2 int
                ,@publisher_id int
                ,@xact_seqno varbinary(16)
                ,@logreader_latency int
                ,@publisher_db sysname
                ,@publication sysname

    
    -- security check
    -- Has to be executed from distribution database
    
    if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
    begin
        raiserror(21482, 16, -1, 'sp_replmonitorrefreshagentdata', 'distribution')
        return (1)
    end
    
    -- logreader specific
    
    if exists (select agent_id from #tmp_replmonitorrefresh where agent_type = 2)
    begin
        
        -- worst_latency in seconds
        -- best_latency  in seconds
        -- avg_latency  in seconds
        
        update #tmp_replmonitorrefresh
        set worst_latency = latencyview.worst/1000
            ,best_latency = latencyview.best/1000
            ,avg_latency = latencyview.average/1000
        from
        (
            select agent_id
                    ,max(delivery_latency) as worst
                    ,min(delivery_latency) as best
                    ,cast(avg(cast(delivery_latency as bigint)) as int) as average
            from dbo.MSlogreader_history with (nolock)
            where runstatus = 2
            group by agent_id
        ) as latencyview
        where #tmp_replmonitorrefresh.agent_id = latencyview.agent_id
            and agent_type = 2
        
        -- cur_latency in seconds
        
        update #tmp_replmonitorrefresh
        set cur_latency = h.delivery_latency/1000
        from
        (
            dbo.MSlogreader_history as h with (nolock)
            join
            (
                select agent_id, max(timestamp) as maxtimestamp
                from dbo.MSlogreader_history with (nolock)
                where runstatus = 2
                and delivered_commands > 0
                group by agent_id
            ) as latest
            on h.agent_id = latest.agent_id
            and h.timestamp = latest.maxtimestamp
        )
        where #tmp_replmonitorrefresh.agent_id = h.agent_id
            and agent_type = 2
        
        -- agentstoptime
        
        update #tmp_replmonitorrefresh
        set agentstoptime = h.time
        from
        (
            dbo.MSlogreader_history as h with (nolock)
            join
            (
                select agent_id, max(timestamp) as maxtimestamp
                from dbo.MSlogreader_history with (nolock)
                where (runstatus = 2 or runstatus > 4)
                group by agent_id
            ) as latest
            on h.agent_id = latest.agent_id
            and h.timestamp = latest.maxtimestamp
        )
        where #tmp_replmonitorrefresh.agent_id = h.agent_id
            and agent_type = 2
    end
    
    -- Distribution agent specific
    
    if exists (select agent_id from #tmp_replmonitorrefresh where (agent_type & 3) = 3)
    begin
        
        -- retention (same for all logbased and snapshot publications)
        -- Use the max_retention value for this distribution db
        
        update #tmp_replmonitorrefresh
        set retention = dbs.max_distretention
        from msdb.dbo.MSdistributiondbs as dbs
        where dbs.name = distdb
            and (agent_type & 3) = 3
        
        -- worst_latency in seconds
        -- best_latency in seconds
        -- avg_latency in seconds
        
        update #tmp_replmonitorrefresh
        set worst_latency = latencyview.worst/1000
            ,best_latency = latencyview.best/1000
            ,avg_latency = latencyview.average/1000
        from
        (
            select agent_id
                    ,max(delivery_latency) as worst
                    ,min(delivery_latency) as best
                    ,cast(avg(cast(delivery_latency as bigint)) as int) as average
            from dbo.MSdistribution_history with (nolock)
            where runstatus = 2
            group by agent_id
        ) as latencyview
        where #tmp_replmonitorrefresh.agent_id = latencyview.agent_id
            and (agent_type & 3) = 3
        
        -- last_distsync (this essentially records the last time
        -- some activity happened on the history of the agent)
        
        update #tmp_replmonitorrefresh
        set last_distsync = h.time
        from
        (
            dbo.MSdistribution_history as h with (nolock)
            join
            (
                select agent_id, max(timestamp) as maxtimestamp
                from dbo.MSdistribution_history with (nolock)
                where runstatus in (2,3,4)
                group by agent_id
            ) as latest
            on h.agent_id = latest.agent_id
            and h.timestamp = latest.maxtimestamp
        )
        where #tmp_replmonitorrefresh.agent_id = h.agent_id
            and (agent_type & 3) = 3
        
        -- agentstoptime
        
        update #tmp_replmonitorrefresh
        set agentstoptime = h.time
        from
        (
            dbo.MSdistribution_history as h with (nolock)
            join
            (
                select agent_id, max(timestamp) as maxtimestamp
                from dbo.MSdistribution_history with (nolock)
                where (runstatus = 2 or runstatus > 4)
                group by agent_id
            ) as latest
            on h.agent_id = latest.agent_id
            and h.timestamp = latest.maxtimestamp
        )
        where #tmp_replmonitorrefresh.agent_id = h.agent_id
            and (agent_type & 3) = 3
        
        -- cur_latency
        -- this value is the ("time of sub commit" - "distribution entry time"/1000
        -- basically this is the distrib agent latency in seconds (pre-computed by add hist)
        
        -- if we are looking at a idle (runstatus = 4) entry then it means there are
        -- no pending messages and we cannot reliably compute cur_latency. We will set it
        -- to zero. This will resolve the cases when some high latency processing happens
        -- and then the latency never goes down if there is no further activity (example:
        -- Initial Snapshot processing followed by no other activity should not spike the
        -- latency for a unduly long time)
        
        update #tmp_replmonitorrefresh
        set cur_latency = case when (distlatency.runstatus = 4) then 0 else distlatency.latency/1000 end
        from
        (
            (
                select agent_id, max(xact_seqno) as maxseqno, max(time) as maxtime
                from dbo.MSdistribution_history with (nolock)
                where runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
                group by agent_id
            ) as xactview
            join
            (
                select agent_id, isnull(delivery_latency, 0) as latency, xact_seqno, time, runstatus
                from dbo.MSdistribution_history with (nolock)
                where runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
            ) as distlatency
            on xactview.agent_id = distlatency.agent_id
            and xactview.maxseqno = distlatency.xact_seqno
            and xactview.maxtime = distlatency.time
        )
        where #tmp_replmonitorrefresh.agent_id = distlatency.agent_id
            and (agent_type & 3) = 3
        
        -- compute the logreader latency for logbased publications
        
        declare #hcrefreshmonitor cursor local fast_forward for
            select publisher_srvid, publisher_db, publication, agent_id
            from #tmp_replmonitorrefresh
            where publication_type = 0
            and (agent_type & 3) = 3
        open #hcrefreshmonitor
        fetch #hcrefreshmonitor into @publisher_id, @publisher_db, @publication, @agent_id
        while (@@fetch_status != -1)
        begin
            
            -- initialize
            
            select @agent_id2 = NULL
                    ,@xact_seqno = NULL
                    ,@logreader_latency = NULL

            select top 1 @xact_seqno = xact_seqno
            from dbo.MSdistribution_history with (nolock)
            where agent_id = @agent_id
                and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
            order by xact_seqno desc, time desc
            
            -- here we are attempting to find the last transaction in logreader history
            -- that is closest to the last transaction delivered to the subscriber.
            -- do logreader latency computation only for logbased publications
            
            -- In the event we find a an idle history log, we will set logreader latency to 0
            
            select @agent_id2 = max(id)
            from dbo.MSlogreader_agents with (nolock)
            where publisher_id = @publisher_id
                and publisher_db = @publisher_db
            if (@agent_id2 is null)
            begin
                raiserror('could not find agent entry', 16,1)
                return (1)
            end
            select top 1 @logreader_latency = case when (runstatus = 4) then 0 else delivery_latency/1000 end
            from MSlogreader_history with (nolock)
            where agent_id = @agent_id2
                and xact_seqno >= @xact_seqno
                and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
            order by xact_seqno asc, time desc
            if @logreader_latency is NULL
            begin
                
                -- if we did not have a tran in front then we will use the
                -- previous one note the difference in order by clause and range
                
                select top 1 @logreader_latency = case when (runstatus = 4) then 0 else delivery_latency/1000 end
                from MSlogreader_history with (nolock)
                where agent_id = @agent_id2
                    and xact_seqno < @xact_seqno
                    and runstatus in (2, 3, 4) -- 2-success 3-inprogress 4-idle
                order by xact_seqno desc, time desc
            end
            
            -- add the latency
            
            if @logreader_latency is not NULL
            begin
                update #tmp_replmonitorrefresh
                set cur_latency = cur_latency + @logreader_latency
                where agent_id = @agent_id
            end
            
            -- fetch next agent
            
            fetch #hcrefreshmonitor into @publisher_id, @publisher_db, @publication, @agent_id
        end
        close #hcrefreshmonitor
        deallocate #hcrefreshmonitor
    end
    
    -- Queuereader agent specific
    
    if exists (select agent_id from #tmp_replmonitorrefresh where agent_type = 9)
    begin
        
        -- @agentstoptime datetime output
        
        update #tmp_replmonitorrefresh
        set agentstoptime = h.time
        from
        (
            dbo.MSqreader_history as h with (nolock)
            join
            (
                select agent_id, max(timestamp) as maxtimestamp
                from dbo.MSqreader_history with (nolock)
                where (runstatus = 2 or runstatus > 4)
                group by agent_id
            ) as latest
            on h.agent_id = latest.agent_id
            and h.timestamp = latest.maxtimestamp
        )
        where #tmp_replmonitorrefresh.agent_id = h.agent_id
            and agent_type = 9
    end
    
    -- Merge agent specific
    
    if exists (select agent_id from #tmp_replmonitorrefresh where (agent_type & 4) = 4)
    begin
        update #tmp_replmonitorrefresh
        set mergelatestsessionconnectiontype = h.connection_type
            ,mergelatestsessionrunduration = h.duration
            ,mergelatestsessionrunspeed=h.delivery_rate
            ,isagentrunningnow=case when (h.runstatus in (1, 3, 4, 5)) then 1 else 0 end
            ,last_distsync = h.start_time
        from dbo.MSmerge_sessions as h with (nolock), #tmp_replmonitorrefresh
        where session_id =
            (
                select top 1 ms2.session_id
                from dbo.MSmerge_sessions ms2 with (nolock)
                where ms2.agent_id = h.agent_id
                order by ms2.session_id desc
            )
        and #tmp_replmonitorrefresh.agent_id = h.agent_id
            and ((agent_type & 4) = 4)
        
        -- runspeed
        
        update #tmp_replmonitorrefresh
        set mergePerformance=
            case when (rates.avg_mergerunspeed is not null and rates.avg_mergerunspeed != 0)
                then CAST((mergelatestsessionrunspeed*100)/rates.avg_mergerunspeed as int)
                else NULL end
        from
        (
            (
                select agent_id, connection_type, delivery_rate
                from dbo.MSmerge_sessions as ms1 with (nolock)
                where (upload_inserts + upload_deletes + upload_updates + download_inserts
                    + download_deletes + download_updates) >= 50
                    and delivery_rate is not null
                    and session_id = (select top 1 ms2.session_id from dbo.MSmerge_sessions ms2 with (nolock)
                        where ms1.agent_id = ms2.agent_id
                        and ms1.connection_type = ms2.connection_type
                        order by ms2.session_id desc)

            ) as latest
            join
            (
                select connection_type
                        ,isnull(avg(delivery_rate),0) as avg_mergerunspeed
                from dbo.MSmerge_sessions with (nolock)
                where delivery_rate is not null
                and (upload_inserts + upload_deletes + upload_updates + download_inserts
                    + download_deletes + download_updates) >= 50
                group by connection_type
                having count(*) >= 5	-- compare with min 5 sessions of same conn type and each having
                                                    -- replicated at least 50 rows.
            ) as rates
            on latest.connection_type = rates.connection_type
        )
        where #tmp_replmonitorrefresh.agent_id = latest.agent_id
            and mergelatestsessionconnectiontype = latest.connection_type
            and ((agent_type & 4) = 4)

    end
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_replmonitorrefreshdata (Procedure)
sp_replmonitorrefreshwarningdata (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash