Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSprocesslogshippingmonitorsecondary

  No additional text.


Syntax
create procedure sys.sp_MSprocesslogshippingmonitorsecondary
(
    @mode tinyint -- 1 = add, 2 = delete, 3 = update
    ,@secondary_server sysname
    ,@secondary_database sysname = NULL
    ,@secondary_id uniqueidentifier
    ,@primary_server sysname = NULL
    ,@monitor_server sysname
    ,@monitor_server_security_mode bit = 1
    ,@primary_database sysname = NULL
    ,@restore_threshold int = NULL
    ,@threshold_alert int = NULL
    ,@threshold_alert_enabled bit = NULL
    ,@last_copied_file nvarchar(500) = NULL
    ,@last_copied_date datetime = NULL
    ,@last_copied_date_utc datetime = NULL
    ,@last_restored_file nvarchar(500) = NULL
    ,@last_restored_date datetime = NULL
    ,@last_restored_date_utc datetime = NULL
    ,@last_restored_latency int = NULL
    ,@history_retention_period int = NULL
    ,@ignoreremotemonitor bit = 0
)
as
begin
    set nocount on
    declare @retcode int
                ,@linkcmd nvarchar(512)

    
    -- validate @mode
    
    if (@mode not in (1,2,3))
        return 1
    
    -- When adding - always delete first
    
    if (@mode in (1,2))
    begin
        
        -- clean up log_shipping_monitor_error_detail
        -- clean up log_shipping_monitor_history_detail
        
        if (@secondary_database is null)
        begin
            select @linkcmd = N'delete msdb.dbo.log_shipping_monitor_error_detail '
            + N'where agent_id = N''' + cast(@secondary_id as nvarchar(50)) + N''' and agent_type in (1,2) '
            exec (@linkcmd)
            select @linkcmd = N'delete msdb.dbo.log_shipping_monitor_history_detail '
            + N'where agent_id = N''' + cast(@secondary_id as nvarchar(50)) + N''' and agent_type in (1,2) '
            exec (@linkcmd)
        end
        else
        begin
            delete msdb.dbo.log_shipping_monitor_error_detail
            where agent_id = @secondary_id and agent_type in (1,2)
                and database_name = @secondary_database
            delete msdb.dbo.log_shipping_monitor_history_detail
            where agent_id = @secondary_id and agent_type in (1,2)
                and database_name = @secondary_database
        end
        
        -- remove entry from log_shipping_monitor_secondary
        
        delete msdb.dbo.log_shipping_monitor_secondary
        where ((@secondary_id is not null
                            and secondary_id = @secondary_id)
                    or (@primary_server is not null and @primary_database is not null and @secondary_server is not null
                            and primary_server = upper(@primary_server)
                            and primary_database = @primary_database
                            and secondary_server = upper(@secondary_server)))
            and (@secondary_database is null or secondary_database = @secondary_database)
        
        -- drop alert job if needed
        
        if (upper(@monitor_server) = upper(@@servername))
        begin
            exec sys.sp_delete_log_shipping_alert_job_internal
        end
        
        -- specific processing for add
        
        if (@mode = 1)
        begin
            
            -- Add an entry in the log_shipping_monitor_secondary
            
            insert into msdb.dbo.log_shipping_monitor_secondary (
                secondary_server
                ,secondary_database
                ,secondary_id
                ,primary_server
                ,primary_database
                ,restore_threshold
                ,threshold_alert
                ,threshold_alert_enabled
                ,last_copied_file
                ,last_copied_date
                ,last_copied_date_utc
                ,last_restored_file
                ,last_restored_date
                ,last_restored_date_utc
                ,last_restored_latency
                ,history_retention_period)
        values (
                upper(@secondary_server)
                ,@secondary_database
                ,@secondary_id
                ,upper(@primary_server)
                ,@primary_database
                ,@restore_threshold
                ,@threshold_alert
                ,@threshold_alert_enabled
                ,@last_copied_file
                ,@last_copied_date
                ,@last_copied_date_utc
                ,@last_restored_file
                ,@last_restored_date
                ,@last_restored_date_utc
                ,@last_restored_latency
                ,@history_retention_period)
            if (@@error != 0)
                return 1
            
            -- add alert job if needed
            
            if (upper(@monitor_server) = upper(@@servername))
            begin
                exec sys.sp_add_log_shipping_alert_job_internal
            end
        end
    end
    else if (@mode = 3)
    begin
        
        -- update log_shipping_monitor_secondary
        
        update msdb.dbo.log_shipping_monitor_secondary
        set restore_threshold = case when (@restore_threshold is null) then restore_threshold else @restore_threshold end
             ,threshold_alert = case when (@threshold_alert is null) then threshold_alert else @threshold_alert end
             ,threshold_alert_enabled = case when (@threshold_alert_enabled is null) then threshold_alert_enabled else @threshold_alert_enabled end
             ,last_copied_file = case when (@last_copied_file is null) then last_copied_file else @last_copied_file end
             ,last_copied_date = case when (@last_copied_date is null) then last_copied_date else @last_copied_date end
             ,last_copied_date_utc = case when (@last_copied_date_utc is null) then last_copied_date_utc else @last_copied_date_utc end
             ,last_restored_file = case when (@last_restored_file is null) then last_restored_file else @last_restored_file end
             ,last_restored_date = case when (@last_restored_date is null) then last_restored_date else @last_restored_date end
             ,last_restored_date_utc = case when (@last_restored_date_utc is null) then last_restored_date_utc else @last_restored_date_utc end
             ,last_restored_latency = case when (@last_restored_latency is null) then last_restored_latency else @last_restored_latency end
             ,history_retention_period = case when (@history_retention_period is null) then history_retention_period else @history_retention_period end
        where secondary_id = @secondary_id
            and (@secondary_database is null or secondary_database = @secondary_database)
        if (@@error != 0)
            return 1
    end
    
    -- Do we have remote monitor
    
    if (upper(@monitor_server) != upper(@secondary_server)
            and upper(@monitor_server) != upper(@@servername)
            and @ignoreremotemonitor = 0)
    begin
        if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
        begin
            
            -- Process remote monitor using proxy
            
            exec @retcode = msdb.sys.sp_MSproxylogshippingmonitorsecondary
                    @mode = @mode
                    ,@secondary_server = @secondary_server
                    ,@secondary_database = @secondary_database
                    ,@secondary_id = @secondary_id
                    ,@primary_server = @primary_server
                    ,@monitor_server = @monitor_server
                    ,@primary_database = @primary_database
                    ,@restore_threshold = @restore_threshold
                    ,@threshold_alert = @threshold_alert
                    ,@threshold_alert_enabled = @threshold_alert_enabled
                    ,@last_copied_file = @last_copied_file
                    ,@last_copied_date = @last_copied_date
                    ,@last_copied_date_utc = @last_copied_date_utc
                    ,@last_restored_file = @last_restored_file
                    ,@last_restored_date = @last_restored_date
                    ,@last_restored_date_utc = @last_restored_date_utc
                    ,@last_restored_latency = @last_restored_latency
                    ,@history_retention_period = @history_retention_period
        end
        else
        begin
            
            -- integrated -do not use proxy
            
            select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.msdb.sys.sp_processlogshippingmonitorsecondary'
            begin try
                exec @retcode = @linkcmd
                    @mode = @mode
                    ,@secondary_server = @secondary_server
                    ,@secondary_database = @secondary_database
                    ,@secondary_id = @secondary_id
                    ,@primary_server = @primary_server
                    ,@monitor_server = @monitor_server
                    ,@monitor_server_security_mode = 1
                    ,@primary_database = @primary_database
                    ,@restore_threshold = @restore_threshold
                    ,@threshold_alert = @threshold_alert
                    ,@threshold_alert_enabled = @threshold_alert_enabled
                    ,@last_copied_file = @last_copied_file
                    ,@last_copied_date = @last_copied_date
                    ,@last_copied_date_utc = @last_copied_date_utc
                    ,@last_restored_file = @last_restored_file
                    ,@last_restored_date = @last_restored_date
                    ,@last_restored_date_utc = @last_restored_date_utc
                    ,@last_restored_latency = @last_restored_latency
                    ,@history_retention_period = @history_retention_period
            end try
            begin catch
                select @retcode = 1
            end catch
        end
        if (@retcode != 0 or @@error != 0)
            return 1
    end
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_add_log_shipping_secondary_database (Procedure)
sp_change_log_shipping_secondary_database (Procedure)
sp_delete_log_shipping_secondary_database_internal (Procedure)
sp_delete_log_shipping_secondary_primary (Procedure)
sp_MSadd_log_shipping_history_detail (Procedure)
sp_processlogshippingmonitorsecondary (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