Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSprocesslogshippingmonitorprimary

  No additional text.


Syntax
create procedure sys.sp_MSprocesslogshippingmonitorprimary
(
    @mode tinyint -- 1 = add, 2 = delete, 3 = update
    ,@primary_id uniqueidentifier
    ,@primary_server sysname = NULL -- needed for add
    ,@monitor_server sysname
    ,@monitor_server_security_mode bit = 1
    ,@primary_database sysname = NULL -- needed for add
    ,@backup_threshold int = NULL
    ,@threshold_alert int = NULL
    ,@threshold_alert_enabled bit = NULL
    ,@last_backup_file nvarchar(500) = NULL
    ,@last_backup_date datetime = NULL
    ,@last_backup_date_utc datetime = 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
        
        select @linkcmd = N'delete msdb.dbo.log_shipping_monitor_error_detail '
        + N'where agent_id = N''' + cast(@primary_id as nvarchar(50)) + N''' and agent_type = 0 '
        exec (@linkcmd)
        
        -- clean up log_shipping_monitor_history_detail
        
        select @linkcmd = N'delete msdb.dbo.log_shipping_monitor_history_detail '
        + N'where agent_id = N''' + cast(@primary_id as nvarchar(50)) + N''' and agent_type = 0 '
        exec (@linkcmd)
        
        -- remove entry from log_shipping_monitor_primary
        
        delete msdb.dbo.log_shipping_monitor_primary
        where (@primary_id is not null
                    and primary_id = @primary_id)
            or (@primary_server is not null and @primary_database is not null
                    and primary_server = upper(@primary_server)
                    and primary_database = @primary_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_primary
            
            insert into msdb.dbo.log_shipping_monitor_primary (
                primary_id
                ,primary_server
                ,primary_database
                ,backup_threshold
                ,threshold_alert
                ,threshold_alert_enabled
                ,last_backup_file
                ,last_backup_date
                ,last_backup_date_utc
                ,history_retention_period)
            values (
                @primary_id
                ,upper(@primary_server)
                ,@primary_database
                ,@backup_threshold
                ,@threshold_alert
                ,@threshold_alert_enabled
                ,@last_backup_file
                ,@last_backup_date
                ,@last_backup_date_utc
                ,@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_primary
        
        update msdb.dbo.log_shipping_monitor_primary
        set backup_threshold = case when (@backup_threshold is null) then backup_threshold else @backup_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_backup_file = case when (@last_backup_file is null) then last_backup_file else @last_backup_file end
             ,last_backup_date = case when (@last_backup_date is null) then last_backup_date else @last_backup_date end
             ,last_backup_date_utc = case when (@last_backup_date_utc is null) then last_backup_date_utc else @last_backup_date_utc end
             ,history_retention_period = case when (@history_retention_period is null) then history_retention_period else @history_retention_period end
        where primary_id = @primary_id
        if (@@error != 0)
            return 1
    end
    
    -- Do we need have remote monitor
    
    if (upper(@monitor_server) != upper(@primary_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_MSproxylogshippingmonitorprimary
                    @mode = @mode
                    ,@primary_id = @primary_id
                    ,@primary_server = @primary_server
                    ,@monitor_server = @monitor_server
                    ,@primary_database = @primary_database
                    ,@backup_threshold = @backup_threshold
                    ,@threshold_alert = @threshold_alert
                    ,@threshold_alert_enabled = @threshold_alert_enabled
                    ,@last_backup_file = @last_backup_file
                    ,@last_backup_date = @last_backup_date
                    ,@last_backup_date_utc = @last_backup_date_utc
                    ,@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_processlogshippingmonitorprimary'
            begin try
                exec @retcode = @linkcmd
                    @mode = @mode
                    ,@primary_id = @primary_id
                    ,@primary_server = @primary_server
                    ,@monitor_server = @monitor_server
                    ,@monitor_server_security_mode = 1
                    ,@primary_database = @primary_database
                    ,@backup_threshold = @backup_threshold
                    ,@threshold_alert = @threshold_alert
                    ,@threshold_alert_enabled = @threshold_alert_enabled
                    ,@last_backup_file = @last_backup_file
                    ,@last_backup_date = @last_backup_date
                    ,@last_backup_date_utc = @last_backup_date_utc
                    ,@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_primary_database (Procedure)
sp_change_log_shipping_primary_database (Procedure)
sp_delete_log_shipping_primary_database (Procedure)
sp_MSadd_log_shipping_history_detail (Procedure)
sp_processlogshippingmonitorprimary (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