Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_replmonitorrefreshdata

  No additional text.


Syntax
create procedure sys.sp_replmonitorrefreshdata
(
    @publisher sysname = NULL  -- null means wildcard
          
    ,@publisher_db sysname = NULL -- null means wildcard
       
    ,@publication sysname = NULL -- null means wildcard
        
    ,@publication_type int = NULL -- null means wildcard
       
    ,@refreshpolicy tinyint = 0 -- 0 = default cache refresh, 1 = optimistic force refresh, 2 = non-optimistic force refresh
       
    ,@profile bit = 0 -- for internal use - DO NOT DOCUMENT (remove this before release)
         
)
as
begin
    set nocount on
           
    declare @retcode int
     
                ,@flockrefreshcheckacquired bit
                ,@flockcomputeacquired bit
                ,@frefresh bit
                ,@fwaitforcacheowner bit
                ,@lastrefresh datetime
                ,@starttime datetime
                ,@endtime datetime
                ,@lastcomputetime int
                ,@ptime datetime
                ,@distribution_db sysname

             

    
    -- initialize
            
    
    select @distribution_db = db_name()
    
    -- validate @refreshpolicy
    
    if (@refreshpolicy not in (0,1,2))
    begin
        raiserror(20587, 16, -1, '@refreshpolicy', 'sp_replmonitorrefreshdata')
        return (1)
           
    end
    
    -- loop till you satisfy the conditions
    
    if (@profile = 1)
        
        select @ptime = getdate()
    while (1=1)
    begin
        
        -- get the exclusive lock for refresh check
            
        
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 0
        if @retcode != 0 or @@error != 0
            return 1
         
        select @flockrefreshcheckacquired = 1
                ,@starttime = getdate()
        
        -- check if we need to refresh
        
        select @lastrefresh = lastrefresh
                            ,@lastcomputetime = case when (4 > isnull(computetime, 0)) then 4 else isnull(3 * computetime, 0) end
  
        from dbo.MSreplication_monitordata
        where lastrefresh is not null

        if @lastrefresh is null
        begin
            -- table has never been populated
            -- switch refreshpolicy to force refresh policy
    
            select @refreshpolicy = 1
                    ,@frefresh = 1
        end
        else
        begin
            
            -- Cache refresh policy
            
            -- default policy (refreshpolicy = 0)
            --   refresh period of max(4sec, lastcomputetime) should have elapsed
            --   if the cache is being processed by others - use older cache
            
            -- optimistic force refresh policy (refreshpolicy = 1)
            --   elapsed refresh period is ignored
             
            --   if the cache is being built by other - block until newly built cache
            
            --   is available and them return the data from newly built cache
            
            -- non-optimistic force refresh policy (refreshpolicy = 2)
            --   elapsed refresh period is ignored
             
            --   if the cache is being built by other - block until cache ownership
            --   is claimed and then rebuild cache again and then return data
            
            if (@refreshpolicy in (1,2)) or ((@refreshpolicy = 0) and datediff(second, @lastrefresh, @starttime) > @lastcomputetime)
                select @frefresh = 1
        end
        
        -- more checks if we are refreshing
        
        if (@frefresh = 1)
   
        begin
            
            -- get the compute lock
            
            exec @retcode = sys.sp_replmonitorsynchronize @mode = 3
            if (@retcode = 0)
            begin
            
                
           
                -- got the compute lock - good to go ahead for refresh
                
           
                select @flockcomputeacquired = 1
                
           
                -- release the lock for refresh check
          
                
           
                exec @retcode = sys.sp_replmonitorsynchronize @mode = 2
                if @@error != 0 or @retcode != 0
                    goto cleanup
                select @flockrefreshcheckacquired = 0
          
                
           
                -- break out of the check loop
                
           
                break
        
            end
            else
             
            begin
            
                
           
                -- someone is already computing the refresh
    
                -- release refresh lock as we will not refresh now
                
           
                select @frefresh = 0
                exec @retcode = sys.sp_replmonitorsynchronize @mode = 2
                if @@error != 0 or @retcode != 0
                    goto cleanup
                select @flockrefreshcheckacquired = 0
          
                
           
                -- processing for force refresh policies
       
                
           
                if (@refreshpolicy in (1,2))
                begin
        
                    
       
                    -- we cannot return until the we own the compute lock
                    -- set the flag to indicate we waited at least once
                    -- delay 1 second and try again
            
                    
       
                    select @fwaitforcacheowner = 1
             
                    waitfor delay '00:00:01'
                    continue
 
                end
          
                else
         
                begin
        
                    
       
                    -- default policy: no refresh is needed since it is being refreshed by someone
                    -- we will not block the processing
        
                    -- break out of loop
                    
       
                    break
    
                end  -- if (@refreshpolicy in (1,2))
           
            end -- did not get compute lock
        end -- if (@frefresh = 1)
        else
        begin
            
            -- no refresh is needed
            -- release the lock for refresh check
            
            exec @retcode = sys.sp_replmonitorsynchronize @mode = 2
            if @@error != 0 or @retcode != 0
                goto cleanup
 
            select @flockrefreshcheckacquired = 0
            
            -- break out of loop
            
            break
            
        end
    end -- end while (1=1)
   
    if (@profile = 1)
        
        select 'applock processing' = datediff(second, @ptime, getdate())
    
    -- For optimistic force refresh (refreshpolicy = 1)
        
    -- check if we waited
   
    
    if (@frefresh = 1 and @flockcomputeacquired = 1
           
        and @refreshpolicy = 1 and @fwaitforcacheowner = 1)
    
    begin
        
        -- we waited and the cache was refreshed by someone else
        -- we will just use that cache instead of refreshing it ourselves
        -- release the compute lock
        
        select @frefresh = 0
 
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 4
        if (@retcode != 0)
   
            goto cleanup
     
        select @flockcomputeacquired = 0
    end
    
    -- consistency check (for debugging purposes)
    
    if (@profile = 1)
        
    begin
        if (@frefresh = 1)
   
        begin
            -- for refresh processing:  compute lock = 1, refresh lock = 0
            if not((@flockrefreshcheckacquired = 0) and (@flockcomputeacquired = 1))
             
            begin
            
                raiserror('Inconsistent synchronization', 16, 1)
                select @flockcomputeacquired, @flockrefreshcheckacquired
                select @retcode = 1
                goto cleanup
 
            end
        end
        else
        begin
            -- for non refresh processing:  compute lock = 0, refresh lock = 0
            if not((@flockrefreshcheckacquired = 0) and (@flockcomputeacquired = 0))
             
            begin
            
                raiserror('Inconsistent synchronization', 16, 1)
                select @flockcomputeacquired, @flockrefreshcheckacquired
                select @retcode = 1
                goto cleanup
 
            end
        end
    end
    
    -- processing block for refresh
    
    if (@frefresh = 1)
       
    begin
        -- we will compute the refresh in a temp table
         
        create table #tmp_replmonitorrefresh
        (
            publication_id int
            ,publisher sysname
            ,publisher_srvid int
            ,publisher_db sysname
            ,publication sysname
            ,publication_type int
            ,agent_type int
 
            ,agent_id int
   
            ,agent_name sysname
            ,job_id uniqueidentifier
            ,status int
      
            ,isagentrunningnow bit
            ,warning int
     
            ,last_distsync datetime
            ,agentstoptime datetime
            ,distdb sysname null
            ,retention int
   
            ,time_stamp datetime null
            ,worst_latency int
            ,best_latency int
            ,avg_latency int
 
            ,cur_latency int
            ,mergePerformance int
            ,mergelatestsessionrunduration int
            ,mergelatestsessionrunspeed float
            ,mergelatestsessionconnectiontype int
            ,retention_period_unit tinyint
        )
        if (@@error != 0)
    
            goto cleanup
     
        
        -- build indices
     
        
        create clustered index ctemprefresh
            on #tmp_replmonitorrefresh(agent_id)
        if (@@error != 0)
    
            goto cleanup
     
        create nonclustered index nc1temprefresh
            on #tmp_replmonitorrefresh(publication_id)
        
        if (@@error != 0)
    
            goto cleanup
     
        create nonclustered index nc2temprefresh
            on #tmp_replmonitorrefresh(agent_type)
            
        if (@@error != 0)
    
            goto cleanup
     

        
        -- create the temp table to load the agent status 
   
        -- since we are using sp_MSload_tmp_replication_status
 
        -- create the temp table for now
        
        create table #tmp_replication_status
        (
            publisher sysname,
            publisher_db sysname,
            publication sysname,
            publication_type int,
            agent_type int, -- this can have 0x80000000 for anonymous distribution/merge agent
   
            status int,
      
            agent_id int,
    
            agent_name sysname,
            job_id uniqueidentifier null,
            time_stamp datetime null,
            publisher_srvid int null
        )

        
        -- load tmp replication_status table
        
        if (@profile = 1)
    
            select @ptime = getdate()
        exec @retcode = sys.sp_MSload_tmp_replication_status @agent_type = -1, @distribution_db = @distribution_db
        if @@error != 0 or @retcode != 0
            goto cleanup
     
        if (@profile = 1)
    
            select 'sp_MSload_tmp_replication_status' = datediff(second, @ptime, getdate())
      
        
        -- Load this data into the refresh table now
           
        
        insert into #tmp_replmonitorrefresh
        (
            publisher
        
            ,publisher_db
    
            ,publication
     
            ,publication_type
            ,agent_type
      
            ,status
          
            ,agent_id
        
            ,agent_name
      
            ,job_id
          
            ,time_stamp
      
            ,distdb
          
            ,publisher_srvid
 
            ,warning
         
            ,publication_id
  
            ,retention
       
            ,retention_period_unit
        )
        select upper(publisher)
            ,t.publisher_db
  
            ,t.publication
   
            ,t.publication_type
            ,agent_type
      
            ,status
          
            ,agent_id
        
            ,agent_name
      
            ,job_id
          
            ,time_stamp
      
            ,@distribution_db
            ,publisher_srvid
 
            ,0
            ,p.publication_id
            ,p.retention
     
            ,p.retention_period_unit
        from #tmp_replication_status t
            left outer join dbo.MSpublications as p with (nolock)
                on t.publisher_srvid = p.publisher_id
         
                and t.publisher_db = p.publisher_db collate database_default
                and t.publication = p.publication collate database_default
                and t.publication_type = p.publication_type
   
        
        -- Agent data and Warning computation
        
        if (@profile = 1)
    
            select @ptime = getdate()
        exec @retcode = sys.sp_replmonitorrefreshagentdata
    
        if @@error != 0 or @retcode != 0
            goto cleanup
     
        if (@profile = 1)
    
        begin
            select 'sp_replmonitorrefreshagentdata' = datediff(second, @ptime, getdate())
        
            select @ptime = getdate()
        end
        exec @retcode = sys.sp_replmonitorrefreshwarningdata
  
        if @@error != 0 or @retcode != 0
            goto cleanup
     
        if (@profile = 1)
    
        begin
            select 'sp_replmonitorrefreshwarningdata' = datediff(second, @ptime, getdate())
      
            select @ptime = getdate()
        end
        
        -- we are done with computing now
        -- we have to update the real table
        -- get the exclusive lock for refresh check
            
        
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 0
        if @retcode != 0
     
            goto cleanup
     
        select @flockrefreshcheckacquired = 1
        if (@profile = 1)
    
        begin
            select 'second sync lock' = datediff(second, @ptime, getdate())
            select @ptime = getdate()
        end
        
        -- populate the monitor refresh table now
        
        truncate table dbo.MSreplication_monitordata
           
        if (@@error != 0)
    
            goto cleanup
     
        insert into dbo.MSreplication_monitordata
        (
            publication_id
  
            ,publisher
      
            ,publisher_srvid
            ,publisher_db
  
            ,publication
   
            ,publication_type
            ,agent_type
    
            ,agent_id
      
            ,agent_name
     
            ,job_id
         
            ,status
         
            ,isagentrunningnow
            ,warning
        
            ,last_distsync
  
            ,agentstoptime
  
            ,distdb
         
            ,retention
      
            ,time_stamp
     
            ,worst_latency
  
            ,best_latency
   
            ,avg_latency
    
            ,cur_latency
     
            ,mergePerformance
            ,mergelatestsessionrunduration
            ,mergelatestsessionrunspeed
            ,mergelatestsessionconnectiontype
            ,retention_period_unit
        )
        select
            publication_id
  
            ,publisher
      
            ,publisher_srvid
            ,publisher_db
  
            ,publication
   
            ,publication_type
            ,agent_type
    
            ,agent_id
      
            ,agent_name
     
            ,job_id
         
            ,status
         
            ,isagentrunningnow
            ,warning
        
            ,last_distsync
  
            ,agentstoptime
  
            ,distdb
         
            ,retention
      
            ,time_stamp
     
            ,worst_latency
  
            ,best_latency
   
            ,avg_latency
    
            ,cur_latency
     
            ,mergePerformance
            ,mergelatestsessionrunduration
            ,mergelatestsessionrunspeed
            ,mergelatestsessionconnectiontype
            ,retention_period_unit
        from #tmp_replmonitorrefresh
        if (@@error != 0)
    
            goto cleanup
     
        
        -- update the compute time of the first row
            
        
        select @endtime = getdate()
        update dbo.MSreplication_monitordata
        set lastrefresh = @endtime
            ,computetime = datediff(second, @starttime, @endtime)
        where lastrefresh is not null

        if @@rowcount = 0
    
        begin
            update top(1) dbo.MSreplication_monitordata
       
            set lastrefresh = @endtime
            ,computetime = datediff(second, @starttime, @endtime)
        end

        if (@profile = 1)
    
        begin
            select 'table swap' = datediff(second, @ptime, getdate())
            select @ptime = getdate()
        end
        
        -- release the compute lock
        
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 4
        if (@retcode != 0)
   
            goto cleanup
     
        select @flockcomputeacquired = 0
        
        -- release the refresh lock
        
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 2
        if @retcode != 0
     
            goto cleanup
     
        select @flockrefreshcheckacquired = 0
    end -- if (@frefresh = 1)
    
    -- do we need to send data back
    
    if object_id('tempdb..#tmp_replication_monitordata') is not null
    begin
        
        -- get the shared lock for refresh check
        
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 1
        if @retcode != 0
    
            goto cleanup
     
        select @flockrefreshcheckacquired = 1
        
        -- get data to send back
        
        if @publisher is not null and @publisher_db is not null and @publication is not null and @publication_type = 2
             
        begin
            insert into #tmp_replication_monitordata
           
            select
          
                publication_id
                ,publisher
  
                ,publisher_srvid
                ,publisher_db
                ,publication
                ,publication_type
                ,agent_type
                ,agent_name
 
                ,job_id
     
                ,status
     
                ,isagentrunningnow
                ,warning
     
                ,last_distsync
                ,agentstoptime
                ,distdb
     
                ,retention
  
                ,time_stamp
                ,worst_latency
                ,best_latency
                ,avg_latency
                ,cur_latency
                ,mergePerformance
                ,mergelatestsessionrunduration
                ,mergelatestsessionrunspeed
                ,mergelatestsessionconnectiontype
                ,retention_period_unit
        from dbo.MSreplication_monitordata
        where upper(publisher) = upper(@publisher)
             
            and publisher_db = @publisher_db
            and publication = @publication
            and publication_type = @publication_type
           
        end
        else
        begin
            insert into #tmp_replication_monitordata
           
            select
          
                publication_id
                ,publisher
  
                ,publisher_srvid
                ,publisher_db
                ,publication
                ,publication_type
                ,agent_type
                ,agent_name
 
                ,job_id
     
                ,status
     
                ,isagentrunningnow
                ,warning
     
                ,last_distsync
                ,agentstoptime
                ,distdb
     
                ,retention
  
                ,time_stamp
                ,worst_latency
                ,best_latency
                ,avg_latency
                ,cur_latency
                ,mergePerformance
                ,mergelatestsessionrunduration
                ,mergelatestsessionrunspeed
                ,mergelatestsessionconnectiontype
                ,retention_period_unit
        from dbo.MSreplication_monitordata
        where upper(publisher) = upper(isnull(@publisher, publisher))
            and (agent_type = 9 -- always include qreader agent entry
                    or (publisher_db = isnull(@publisher_db, publisher_db)))
            and (@publication is NULL or publication in (@publication, N'ALL'))
            and publication_type = isnull(@publication_type, publication_type)
        end

        if @@error != 0
      
            goto cleanup
     
        
        -- release the refresh lock
        
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 2
        if @retcode != 0
     
            goto cleanup
     
        select @flockrefreshcheckacquired = 0
        if (@profile = 1)
    
        begin
            select 'data population to send back to caller' = datediff(second, @ptime, getdate())
            select @ptime = getdate()
        end
    end
    
    -- release the locks
     
    
cleanup:
    if (@flockcomputeacquired = 1)
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 4
    if (@flockrefreshcheckacquired = 1)
        exec @retcode = sys.sp_replmonitorsynchronize @mode = 2
    
    -- set the retcode if there was error
    
    if @@error != 0
          
        select @retcode = 1
  
    
    -- all done
    
    return @retcode
          
end

 
Last revision 2008RTM
See also

  sp_replmonitorhelppublication (Procedure)
sp_replmonitorhelppublisherhelper (Procedure)
sp_replmonitorhelpsubscription (Procedure)
sp_replmonitorrefreshjob (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