create procedure sys.sp_change_log_shipping_secondary_database
(
    @secondary_database sysname -- cannot be NULL
    ,@restore_delay int = NULL
    ,@restore_all bit = NULL
    ,@restore_mode bit = NULL
    ,@disconnect_users bit = NULL
    ,@block_size int = NULL
    ,@buffer_count int = NULL
    ,@max_transfer_size int = NULL
    ,@restore_threshold int = NULL
    ,@threshold_alert int = NULL
    ,@threshold_alert_enabled bit = NULL
    ,@history_retention_period int = NULL
    ,@ignoreremotemonitor bit = 0
)
as
begin
    set nocount on
    declare @retcode int
                ,@secondary_id uniqueidentifier
                ,@primary_server sysname
                ,@primary_database sysname
                ,@monitor_server sysname
                ,@monitor_server_security_mode bit

    
    -- security check
    
    exec @retcode = sys.sp_MSlogshippingsysadmincheck
    if (@retcode != 0 or @@error != 0)
        return 1
    
    -- must be invoked from master db
    
    if (db_name() != N'master')
    begin
        raiserror(5001, 16,-1)
        return 1
    end
    
    -- Does it exist
    
    select @secondary_id = sd.secondary_id
            ,@primary_server = s.primary_server
            ,@primary_database = s.primary_database
            ,@monitor_server = s.monitor_server
            ,@monitor_server_security_mode = s.monitor_server_security_mode
    from msdb.dbo.log_shipping_secondary_databases as sd join msdb.dbo.log_shipping_secondary as s
        on sd.secondary_id = s.secondary_id
    where sd.secondary_database = @secondary_database
    if (@secondary_id is null)
    begin
        raiserror(32014, 16, 1, @secondary_database)
        return 1
    end
    
    -- validate block_size
    
    if (@block_size is not null and (@block_size < -1 or @block_size > 65536))
    begin
        raiserror(21055, 16, -1, N'@block_size', N'sp_change_log_shipping_secondary_database')
        return 1
    end
    
    -- validate buffer_count
    
    if (@buffer_count is not null and @buffer_count < -1)
    begin
        raiserror(21055, 16, -1, N'@buffer_count', N'sp_change_log_shipping_secondary_database')
        return 1
    end
    
    -- validate buffer_count
    
    if (@max_transfer_size is not null and @max_transfer_size < -1)
    begin
        raiserror(21055, 16, -1, N'@max_transfer_size', N'sp_change_log_shipping_secondary_database')
        return 1
    end
    
    -- validate @threshold_alert
    
    if (@threshold_alert is not null and @threshold_alert != 14421)
    begin
        if not exists (select *
            from master.dbo.sysmessages where error = @threshold_alert)
        begin
            raiserror(32028, 16, 4, @threshold_alert)
            return 1
        end
    end
    
    -- start transaction
    
    begin tran sp_change_ls_sd
    save tran sp_change_ls_sd
    
    -- update log_shipping_secondary_databases
    
    update msdb.dbo.log_shipping_secondary_databases
    set restore_delay = case when (@restore_delay is null) then restore_delay else @restore_delay end
         ,restore_all = case when (@restore_all is null) then restore_all else @restore_all end
         ,restore_mode = case when (@restore_mode is null) then restore_mode else @restore_mode end
         ,disconnect_users = case when (@disconnect_users is null) then disconnect_users else @disconnect_users end
         ,block_size = case when (@block_size is null) then block_size else @block_size end
         ,buffer_count = case when (@buffer_count is null) then buffer_count else @buffer_count end
         ,max_transfer_size = case when (@max_transfer_size is null) then max_transfer_size else @max_transfer_size end
    where secondary_id = @secondary_id
        and secondary_database = @secondary_database
    if (@@error != 0)
        goto UNDO
    
    -- commit
    
    commit tran
    
    -- Add a monitor metadata
    
    exec @retcode = sp_MSprocesslogshippingmonitorsecondary @mode = 3
                ,@secondary_server = @@servername
                ,@secondary_database = @secondary_database
                ,@secondary_id = @secondary_id
                ,@primary_server = @primary_server
                ,@monitor_server = @monitor_server
                ,@monitor_server_security_mode = @monitor_server_security_mode
                ,@primary_database = @primary_database
                ,@restore_threshold = @restore_threshold
                ,@threshold_alert = @threshold_alert
                ,@threshold_alert_enabled = @threshold_alert_enabled
                ,@history_retention_period = @history_retention_period
                ,@ignoreremotemonitor = @ignoreremotemonitor
    if (@retcode != 0 or @@error != 0)
        return 1
    
    -- all done
    
    return 0

UNDO:
    rollback tran sp_change_ls_sd
    commit tran
    return 1
end