Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_replmonitorrefreshwarningdata

  No additional text.


Syntax
create procedure sys.sp_replmonitorrefreshwarningdata
as
begin
    set nocount on
    declare @retcode int
                ,@publication_id int
                ,@publication sysname
                ,@metric_id int
                ,@title sysname
                ,@description sysname
                ,@alertmessageid int
                ,@value sql_variant
                ,@shouldalert bit
                ,@warningbitstatus int
                ,@thresholdvalue int
                ,@cutoff_time datetime
                ,@warning int
                ,@cur_latency int
                ,@agent_type int
                ,@publication_type int
    
    -- constants
    
    declare @expiration int
                ,@latency int
                ,@mergeexpirationChosen int
                ,@mergefastrundurationChosen int
                ,@mergeslowrundurationChosen int
                ,@mergefastrunspeedChosen int
                ,@mergeslowrunspeedChosen int
                ,@retention_period_unit tinyint

    
    -- initialize constants
    
    select @expiration = 1
                ,@latency = 2
                ,@mergeexpirationChosen=4
                ,@mergeslowrundurationChosen=5
                ,@mergefastrundurationChosen=6
                ,@mergefastrunspeedChosen=7
                ,@mergeslowrunspeedChosen=8

    
    -- 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

    
    -- process all the necessary publications
    
    declare #hccomputewarning cursor local fast_forward for
        select t.publication_id, t.metric_id, t.value, t.shouldalert
            ,m.title, m.warningbitstatus, m.alertmessageid, m.description
            ,msp.publication_type, msp.publication
        from dbo.MSpublicationthresholds as t with (nolock), msdb.dbo.MSreplmonthresholdmetrics as m, dbo.MSpublications as msp with (nolock)
        where t.metric_id = m.metric_id
        and t.isenabled=1
        and msp.publication_id = t.publication_id
        and t.publication_id in
            (select distinct publication_id
            from #tmp_replmonitorrefresh)

    open #hccomputewarning
    fetch #hccomputewarning into @publication_id, @metric_id, @value, @shouldalert, @title, @warningbitstatus, @alertmessageid, @description, @publication_type, @publication
    while (@@fetch_status != -1)
    begin
        
        -- reset warning variable
        
        select @warning = NULL
                ,@thresholdvalue = NULL
        
        -- Process based on publication type
        
        if  @publication_type <> 2
        begin
            
            -- transactional/snapshot publication
            -- Process based on metric
            
            if (@metric_id = @expiration)
            begin
                if (@value is not null)
                begin
                    
                    -- Tran retention is based on max retention of distribution db
                    
                    select @thresholdvalue = (max(isnull(retention,0)) * cast(@value as int)) / 100
                    from #tmp_replmonitorrefresh
                    where publication_id = @publication_id
                        and (agent_type & 3) = 3

                    if (@thresholdvalue > 0)
                    begin
                        
                        -- Set the flag for expiration threshold for the specific subscriptions
                        -- (distribution agent entries) that are ready to expire within given threshold
                        
                        select @cutoff_time = dateadd(hour, -@thresholdvalue, getdate())
                        update #tmp_replmonitorrefresh
                        set warning = warning | (case when (@warning is null) then @warningbitstatus
                                                            else (@warning | @warningbitstatus) end)
                        where publication_id = @publication_id
                            and (agent_type & 3) = 3
                            and last_distsync < @cutoff_time
                        if (@@rowcount > 0)
                        begin
                            
                            -- update the current warning status for the publication
                            
                            select @warning = case when (@warning is null) then @warningbitstatus
                                                                else (@warning | @warningbitstatus) end
                        end
                    end
                end
            end
            else if (@metric_id = @latency)
            begin
                if (@value is not null)
                begin
                    
                    -- update the subscriptions(distribution agent entries) whose latency exceed
                    -- the value
                    
                    update #tmp_replmonitorrefresh
                    set warning = warning | (case when (@warning is null) then @warningbitstatus
                                                                else (@warning | @warningbitstatus) end)
                    where publication_id = @publication_id
                            and (agent_type & 3) = 3
                            and isnull(cur_latency,0) > cast(@value as int)
                    if @@rowcount > 0
                    begin
                        
                        -- update the current warning status for the publication
                        
                        select @warning = case when (@warning is null) then @warningbitstatus
                                                            else (@warning | @warningbitstatus) end
                    end
                end
            end
            /*
            else if (@metric_id = @stoppedagent)
            begin
                if (@value is not null)
                begin
                    
                    -- If an agent (logreader, distribution, queuereader) has
                    -- been not been running for given threshold
                    -- set the flag
                    
                    select @thresholdvalue = cast(@value as int)
                    select @cutoff_time = dateadd(minute, -@thresholdvalue, getdate())
                    update #tmp_replmonitorrefresh
                    set warning = warning | (case when (@warning is null) then @warningbitstatus
                                                                else (@warning | @warningbitstatus) end)
                    where publication_id = @publication_id
                                and (agent_type in (2,9) or (agent_type & 3) = 3)
                                and agentstoptime is not null
                                and agentstoptime < @cutoff_time
                    if @@rowcount > 0
                    begin
                        
                        -- update the current warning status for the publication
                        
                        select @warning = case when (@warning is null) then @warningbitstatus
                                                            else (@warning | @warningbitstatus) end
                    end
                end
            end
            */
        end -- transactional/snapshot publication
        else
        begin
            
            -- merge publication
            -- Process based on metric
            
            if (@metric_id = @mergeexpirationChosen)
            begin
                if (@value is not null)
                begin
                select top 1 @thresholdvalue = (isnull(retention,0) * cast(@value as int)) / 100,
                                    @retention_period_unit = retention_period_unit
                    from #tmp_replmonitorrefresh
                    where publication_id = @publication_id
                    order by retention desc

                    if (@thresholdvalue > 0)
                    begin
                        select @cutoff_time = sys.fn_add_units_to_date(
                                                                        -@thresholdvalue,
                                                                        @retention_period_unit,
                                                                        getdate())
                        
                        -- Set the flag for expiration threshold if there exist any subscriptions
                        -- that are ready to expire within given threshold
                        
                        update #tmp_replmonitorrefresh
                        set warning = warning | (case when (@warning is null) then @warningbitstatus
                                                                        else (@warning | @warningbitstatus) end)
                        where agent_id in
                            (select sess.agent_id
                                            from dbo.MSmerge_sessions as sess with (nolock),
                                                dbo.MSmerge_agents as agt with (nolock),
                                                dbo.MSpublications as pubs with (nolock),
                                                (
                                                    select agent_id, max(start_time) as maxtime
                                                    from dbo.MSmerge_sessions with (nolock)
                                                    group by agent_id
                                                ) as latest
                                            where pubs.publication_id= @publication_id
                                                and pubs.publisher_id = agt.publisher_id
                                                and pubs.publisher_db = agt.publisher_db
                                                and pubs.publication = agt.publication
                                                and agt.id=sess.agent_id
                                                and sess.start_time=latest.maxtime
                                                and sess.agent_id=latest.agent_id
                                                and sess.end_time < @cutoff_time)
                            and (agent_type & 4) = 4
                        if @@rowcount > 0
                        begin
                            
                            -- update the current warning status for the publication
                            
                            select @warning = case when (@warning is null) then @warningbitstatus
                                                                else (@warning | @warningbitstatus) end
                        end
                    end
                end
            end
            else if (@metric_id = @mergefastrundurationChosen)
            begin
                if (@value is not null)
                begin
                    update #tmp_replmonitorrefresh
                    set warning = warning | (case when (@warning is null) then @warningbitstatus
                                                                else (@warning | @warningbitstatus) end)
                    where publication_id = @publication_id
                        and mergelatestsessionrunduration>@value
                        and mergelatestsessionconnectiontype=1 -- LAN
                        and (agent_type & 4) = 4
                    if @@rowcount > 0
                    begin
                    
                        -- update the current warning status for the publication
                        
                        select @warning = case when (@warning is null) then @warningbitstatus
                                                            else (@warning | @warningbitstatus) end
                    end
                end
            end
            else if (@metric_id = @mergeslowrundurationChosen)
            begin
                if (@value is not null)
                begin
                    update #tmp_replmonitorrefresh
                    set warning = warning | (case when (@warning is null) then @warningbitstatus
                                                                    else (@warning | @warningbitstatus) end)
                    where publication_id = @publication_id
                        and mergelatestsessionrunduration>@value
                        and mergelatestsessionconnectiontype=2 -- DUN
                        and (agent_type & 4) = 4
                    if @@rowcount > 0
                    begin
                        
                        -- update the current warning status for the publication
                        
                        select @warning = case when (@warning is null) then @warningbitstatus
                                                            else (@warning | @warningbitstatus) end
                    end
                end
            end
            else if (@metric_id = @mergefastrunspeedChosen)
            begin
                if (@value is not null)
                begin
                    -- compare latest speed with chosen speed
                    update #tmp_replmonitorrefresh
                    set warning = warning | (case when (@warning is null) then @warningbitstatus
                                                                else (@warning | @warningbitstatus) end)
                        where agent_id in
                            (select sess.agent_id
                                from dbo.MSmerge_sessions as sess with (nolock),
                                dbo.MSmerge_agents as agt with (nolock),
                                dbo.MSpublications as pubs with (nolock),
                                (
                                select agent_id, max(start_time) as maxtime
                                from dbo.MSmerge_sessions with (nolock)
                                where connection_type=1 -- LAN
                                group by agent_id
                                ) as latest
                                where pubs.publication_id= @publication_id
                                    and pubs.publisher_id = agt.publisher_id
                                    and pubs.publisher_db = agt.publisher_db
                                    and pubs.publication = agt.publication
                                    and agt.id=sess.agent_id
                                    and sess.start_time=latest.maxtime
                                    and sess.agent_id=latest.agent_id
                                    and sess.delivery_rate < @value
                                    and (sess.upload_inserts + sess.upload_deletes + sess.upload_updates + sess.download_inserts
                                                        + sess.download_deletes + sess.download_updates) >= 50
                                    and sess.delivery_rate is not null)
                            and (agent_type & 4) = 4
                    if @@rowcount > 0
                    begin
                        
                        -- update the current warning status for the publication
                        
                        select @warning = case when (@warning is null) then @warningbitstatus
                                                            else (@warning | @warningbitstatus) end
                    end
                end
            end
            else if (@metric_id = @mergeslowrunspeedChosen)
            begin
                if (@value is not null)
                begin
                    -- compare latest speed with chosen speed
                    update #tmp_replmonitorrefresh
                    set warning = warning | (case when (@warning is null) then @warningbitstatus
                                                                    else (@warning | @warningbitstatus) end)
                        where agent_id in
                            (select sess.agent_id
                                from dbo.MSmerge_sessions as sess with (nolock),
                                dbo.MSmerge_agents as agt with (nolock),
                                dbo.MSpublications as pubs with (nolock),
                                (
                                select agent_id, max(start_time) as maxtime
                                from dbo.MSmerge_sessions with (nolock)
                                where connection_type=2 -- DUN
                                group by agent_id
                                ) as latest
                                where pubs.publication_id= @publication_id
                                    and pubs.publisher_id = agt.publisher_id
                                    and pubs.publisher_db = agt.publisher_db
                                    and pubs.publication = agt.publication
                                    and agt.id=sess.agent_id
                                    and sess.start_time=latest.maxtime
                                    and sess.agent_id=latest.agent_id
                                    and sess.delivery_rate < @value
                                    and (sess.upload_inserts + sess.upload_deletes + sess.upload_updates + sess.download_inserts
                                                            + sess.download_deletes + sess.download_updates) >= 50
                                    and sess.delivery_rate is not null)
                            and (agent_type & 4) = 4
                    if @@rowcount > 0
                    begin
                        
                        -- update the current warning status for the publication
                        
                        select @warning = case when (@warning is null) then @warningbitstatus
                                                            else (@warning | @warningbitstatus) end
                    end
                end
            end
        end -- if  else (@publication_type <> 2)

        if (@warning is not null)
        begin
            -- Raise alert if needed
            if (@shouldalert = 1)
                raiserror(@alertmessageid, 10, -1, @title, @description, @publication)
        end
        
        -- fetch next threshold
        
        fetch #hccomputewarning into @publication_id, @metric_id, @value, @shouldalert, @title, @warningbitstatus, @alertmessageid, @description, @publication_type, @publication

    end -- (@@fetch_status != -1)

    close #hccomputewarning
    deallocate #hccomputewarning
end

 
Last revision 2008RTM
See also

  sp_replmonitorrefreshdata (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