Como posso automatizar o processo de monitoramento de envio de logs

O script abaixo fornecerá a maioria das informações dos relatórios padrão no SSMS quando executado em seu servidor secundário.

select ls.primary_server,ls.primary_database,lsd.restore_delay,
DATEDIFF(mi,lms.last_restored_date,getdate()) as time_since_last_restore,
lms.last_copied_date,lms.last_restored_date,lms.last_copied_file,
lms.last_restored_file,
lsd.disconnect_users,ls.backup_source_directory,
ls.backup_destination_directory,ls.monitor_server


from msdb.dbo.log_shipping_secondary ls
join msdb.dbo.log_shipping_secondary_databases lsd
  on lsd.secondary_id=ls.secondary_id
join msdb.dbo.log_shipping_monitor_secondary lms
  on lms.secondary_id=lsd.secondary_id

Você pode agendar isso para ser executado diariamente com um trabalho do SQL Agent ou, se tiver o SSRS, pode agendar e formatar facilmente o relatório.

Se você seguir a rota do SQL Agent, precisará configurar o Database Mail e usá-lo como seu serviço de entrega para os resultados da consulta.

Olá pessoal, depois de muita pesquisa e depois de testar em um dos meus servidores, usei a consulta abaixo com pequenas modificações e fornece resultados perfeitos. Espero que esta consulta seja útil para buscar o relatório de status do LS.

USE [master]
GO

begin
    set nocount on
    DECLARE @Recipients varchar(275)
    DECLARE @Subject varchar(275)
    DECLARE @Body varchar(MAX)
    DECLARE @Server varchar(25)         
    DECLARE @idx int, @Status varchar(25), @DB varchar(25), @LastCopy varchar(6), @LastCFile varchar(250),
        @LastRestore varchar(6), @LastRFile varchar(250), @Latency varchar(3), @Threshold varchar(3)
    declare @retcode int
                ,@primary_id uniqueidentifier
                ,@primary_server sysname
                ,@primary_database sysname
                ,@backup_threshold int
                ,@is_backup_alert_enabled bit

                ,@secondary_id uniqueidentifier
                ,@secondary_server sysname
                ,@secondary_database sysname
                ,@restore_threshold int
                ,@is_restore_alert_enabled bit
                ,@last_copied_file nvarchar(500)
                ,@last_copied_utc datetime
                ,@time_since_last_copy int
                ,@last_restored_file nvarchar(500)
                ,@last_restored_utc datetime
                ,@time_since_last_restore int
                ,@last_restored_latency int

                ,@prev_primary_server sysname
                ,@prev_primary_database sysname

                ,@monitor_server sysname
                ,@monitor_server_security_mode int
                ,@is_monitor_local bit
                ,@curutcdate datetime
                ,@linkcmd nvarchar(4000)

SET @Recipients = '[email protected]'
SET @Body = ''
SET @Server = @@SERVERNAME
SET @Subject = @Server + ' :: Daily Transaction Log Shipping Status'    
SET @Body = '<p style="font-size:12px;font-family:Verdana"><b>' + @Server + ': </b></font><hr style="width: 100%; height: 1px;"> '  
    create table #log_shipping_monitor
    (
        idx int identity(1,1)
        ,status bit null
        ,is_primary bit not null default 0
        ,server sysname 
        ,database_name sysname
        ,is_backup_alert_enabled bit null
        ,time_since_last_copy int null
        ,last_copied_file nvarchar(500) null
        ,time_since_last_restore int null
        ,last_restored_file nvarchar(500) null
        ,last_restored_latency int null
        ,restore_threshold int null
        ,is_restore_alert_enabled bit null
        ,ts timestamp not null
        ,primary key (is_primary, server, database_name)
        ,unique (ts)
    )

    --
    -- create other tables we will use
    --
    create table #secondary_monitor
    (
        secondary_server sysname not null,
        secondary_database sysname not null,
        secondary_id uniqueidentifier not null,
        primary_server sysname not null,
        primary_database sysname not null,
        restore_threshold int not null, 
        threshold_alert int not null, 
        threshold_alert_enabled bit not 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 not null, 
        primary key (secondary_id, secondary_database)
    )

    create table #primary_monitor 
    (
        primary_id uniqueidentifier primary key not null,
        primary_server sysname not null,
        primary_database sysname not null,
        backup_threshold int not null,  
        threshold_alert int not null, 
        threshold_alert_enabled bit not null, 
        last_backup_file nvarchar(500) null, 
        last_backup_date datetime null, 
        last_backup_date_utc datetime null, 
        history_retention_period int not null,
        unique (primary_server, primary_database)
    )

    --
    -- get current time
    --
    select @curutcdate = getutcdate()
    --
    -- Enumerate the primary entries
    --
    declare #hcprimaries cursor local fast_forward for
        select 
            primary_id
            ,primary_server 
            ,primary_database 
            ,backup_threshold 
            ,threshold_alert_enabled
        from msdb.dbo.log_shipping_monitor_primary with (nolock)
        order by primary_server, primary_database

    open #hcprimaries
    fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
    while (@@fetch_status != -1)
    begin
        --
        -- we have a primary entry
        --
        insert into #log_shipping_monitor (
            status
            ,is_primary
            ,server
            ,database_name
            ,is_backup_alert_enabled)
        values (
            1
            ,@primary_server
            ,@primary_database
            ,@backup_threshold
            ,@is_backup_alert_enabled)
        --
        -- process secondaries
        --
        if (upper(@primary_server) = upper(@@servername))
        begin
            --
            -- we are on primary server
            -- get monitor server information
            --
            select @monitor_server = monitor_server
                        ,@monitor_server_security_mode = monitor_server_security_mode
            from msdb.dbo.log_shipping_primary_databases
            where primary_id = @primary_id
            select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
            --
            -- enumerate the secondaries listed on primary
            --
            declare #hcprimarysecondaries cursor local fast_forward for
                select secondary_server, secondary_database
                from msdb.dbo.log_shipping_primary_secondaries with (nolock)
                where primary_id = @primary_id

            open #hcprimarysecondaries
            fetch #hcprimarysecondaries into @secondary_server, @secondary_database
            while (@@fetch_status != -1)
            begin
                --
                -- add this primary secondary to result set
                --
                insert into #log_shipping_monitor (is_primary ,server, database_name)
                    values (0, @secondary_server, @secondary_database)
                select @secondary_id = NULL
                --
                -- Enumerate this secondary from msdb.dbo.log_shipping_monitor_secondary
                --
                if (@is_monitor_local = 1)
                begin
                    --
                    -- local monitor
                    --
                    select  
                        @secondary_id = secondary_id
                        ,@restore_threshold = restore_threshold
                        ,@is_restore_alert_enabled = threshold_alert_enabled
                        ,@last_copied_file = last_copied_file
                        ,@last_copied_utc = last_copied_date_utc
                        ,@last_restored_file = last_restored_file
                        ,@last_restored_utc = last_restored_date_utc
                        ,@last_restored_latency = last_restored_latency
                    from msdb.dbo.log_shipping_monitor_secondary
                    where primary_server = upper(@primary_server)
                        and primary_database = @primary_database
                        and secondary_server = upper(@secondary_server)
                        and secondary_database = @secondary_database
                end -- local monitor
                else
                begin
                    --
                    -- remote monitor
                    --
                    if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
                    begin
                        --
                        -- execute as proxy
                        --
                        exec @retcode = sys.sp_MSproxylogshippingmonitorhelpsecondary
                                @monitor_server = @monitor_server
                                ,@p1 = @primary_server
                                ,@p2 = @primary_database
                                ,@p3 = @secondary_server
                                ,@p4 = @secondary_database
                                ,@p5 = @secondary_id output
                                ,@p6 = @restore_threshold output
                                ,@p7 = @is_restore_alert_enabled output
                                ,@p8 = @last_copied_file output
                                ,@p9 = @last_copied_utc output
                                ,@p10 = @last_restored_file output
                                ,@p11 = @last_restored_utc output
                                ,@p12 = @last_restored_latency output                                
                    end
                    else
                    begin
                        delete #secondary_monitor
                        select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_secondary '
                                ,@retcode = 0
                        begin try
                            insert into #secondary_monitor
                            exec @retcode = @linkcmd 
                                        @secondary_server = @secondary_server
                                        ,@secondary_database = @secondary_database
                        end try
                        begin catch
                            select @retcode = 1
                                    ,@secondary_id = NULL
                        end catch

                        if (@retcode = 0)
                        begin
                            select @secondary_id = secondary_id
                                    ,@restore_threshold = restore_threshold
                                    ,@is_restore_alert_enabled = threshold_alert_enabled
                                    ,@last_copied_file = last_copied_file
                                    ,@last_copied_utc = last_copied_date_utc
                                    ,@last_restored_file = last_restored_file
                                    ,@last_restored_utc = last_restored_date_utc
                                    ,@last_restored_latency = last_restored_latency 
                            from #secondary_monitor
                            where upper(primary_server) = upper(@primary_server)
                                and primary_database = @primary_database
                        end
                        else
                        begin
                            raiserror(32031, 10, 1, @secondary_server, @secondary_database, @monitor_server)
                        end
                    end
                end -- remote monitor
                --
                -- do we have data on this secondary
                --
                if (@secondary_id is not null)
                begin
                    --
                    -- yes we do - update the entry
                    --
                    select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
                            ,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
                    update #log_shipping_monitor
                    set
                        status = case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end
                        ,time_since_last_copy = @time_since_last_copy
                        ,last_copied_file = @last_copied_file
                        ,time_since_last_restore = @time_since_last_restore
                        ,last_restored_file = @last_restored_file
                        ,last_restored_latency = @last_restored_latency
                        ,restore_threshold = @restore_threshold
                        ,is_restore_alert_enabled = @is_restore_alert_enabled
                    where upper(server) = upper(@secondary_server)
                        and database_name = @secondary_database
                end -- update secondary data
                --
                -- fetch next primary secondary
                --
                fetch #hcprimarysecondaries into @secondary_server, @secondary_database
            end
            close #hcprimarysecondaries
            deallocate #hcprimarysecondaries
        end -- we are on primary server processing primaries
        else
        begin
            --
            -- we are on monitor server
            -- get details of the secondaries from msdb.dbo.log_shipping_monitor_secondary
            -- if the same monitor is being used by secondaries
            --
            insert into #log_shipping_monitor (
                status 
                ,is_primary 
                ,server  
                ,database_name 
                ,time_since_last_copy 
                ,last_copied_file 
                ,time_since_last_restore 
                ,last_restored_file 
                ,last_restored_latency 
                ,restore_threshold 
                ,is_restore_alert_enabled)
            select 
                case when (datediff(minute, last_restored_date_utc, @curutcdate) > restore_threshold
                                        or last_restored_latency > restore_threshold) then 1 else 0 end
                ,0
                ,secondary_server
                ,secondary_database
                ,datediff(minute, last_copied_date_utc, @curutcdate)
                ,last_copied_file
                ,datediff(minute, last_restored_date_utc, @curutcdate)
                ,last_restored_file
                ,last_restored_latency
                ,restore_threshold
                ,threshold_alert_enabled
            from msdb.dbo.log_shipping_monitor_secondary (nolock)
            where primary_server = upper(@primary_server)
                and primary_database = @primary_database

        end -- we are on monitor server processing primaries
        fetch #hcprimaries into @primary_id, @primary_server, @primary_database, @backup_threshold, @is_backup_alert_enabled
    end -- while cursor for hcprimaries
    close #hcprimaries
    deallocate #hcprimaries
    --
    -- Enumerate the secondary entries
    -- minus existing secondary entries in resultset
    --
    declare #hcsecondaries cursor local fast_forward for
        select 
            secondary_server
            ,secondary_database
            ,secondary_id uniqueidentifier
            ,primary_server
            ,primary_database
            ,restore_threshold
            ,threshold_alert_enabled
            ,last_copied_file
            ,last_copied_date_utc
            ,last_restored_file
            ,last_restored_date_utc
            ,last_restored_latency
        from msdb.dbo.log_shipping_monitor_secondary with (nolock) 
        where not exists (select * from #log_shipping_monitor
                                where upper(server) = upper(secondary_server)
                                    and database_name = secondary_database
                                    and is_primary = 0)
        order by primary_server, primary_database

    open #hcsecondaries
    fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
                @primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled, 
                @last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
    while (@@fetch_status != -1)
    begin
        --
        -- Have we processed the primary for this secondary
        --
        if not (upper(@primary_server) = upper(@prev_primary_server)
            and @primary_database = @prev_primary_database)
        begin
            --
            -- No - Try to get the details of this primary
            --
            select @primary_id = null
            if (upper(@secondary_server) = upper(@@servername))
            begin
                --
                -- we are on secondary
                -- get monitor server information
                --
                select @monitor_server = monitor_server
                        ,@monitor_server_security_mode = monitor_server_security_mode
                from msdb.dbo.log_shipping_secondary with (nolock)
                where secondary_id = @secondary_id
                select @is_monitor_local = case when (upper(@monitor_server) = upper(@@servername)) then 1 else 0 end
                if (@is_monitor_local = 1)
                begin
                    --
                    -- local monitor
                    --
                    select @primary_id = primary_id
                        ,@backup_threshold = backup_threshold
                        ,@is_backup_alert_enabled = threshold_alert_enabled
                    from msdb.dbo.log_shipping_monitor_primary with (nolock)
                    where primary_server = upper(@primary_server)
                        and primary_database = @primary_database
                end
                else
                begin
                    --
                    -- remote monitor
                    --
                    if (@monitor_server_security_mode = 0) and (suser_name() != SUSER_SNAME(0x01))
                    begin
                        --
                        -- execute as proxy
                        --
                        exec @retcode = sys.sp_MSproxylogshippingmonitorhelpprimary
                                @monitor_server = @monitor_server
                                ,@p1 = @primary_server
                                ,@p2 = @primary_database
                                ,@p3 = @primary_id output
                                ,@p4 = @backup_threshold output
                                ,@p5 = @is_backup_alert_enabled output
                    end
                    else
                    begin
                        delete #primary_monitor
                        select @linkcmd = quotename(sys.fn_MSgetlogshippingmoniterlinkname(upper(@monitor_server))) + N'.master.sys.sp_help_log_shipping_monitor_primary '
                                ,@retcode = 0
                        begin try
                            insert into #primary_monitor
                            exec @retcode = @linkcmd 
                                        @primary_server = @primary_server
                                        ,@primary_database = @primary_database
                        end try
                        begin catch
                            select @retcode = 1
                                    ,@primary_id = NULL
                        end catch

                        if (@retcode = 0)
                        begin
                            select @primary_id = primary_id
                                    ,@backup_threshold = backup_threshold
                                    ,@is_backup_alert_enabled = threshold_alert_enabled
                            from #primary_monitor
                        end
                        else
                        begin
                            raiserror(32030, 10, 1, @primary_server, @primary_database, @monitor_server)
                        end
                    end -- processing remote
                end
            end -- processing on secondary
            else
            begin
                --
                -- we are on monitor server
                --
                select @primary_id = primary_id
                    ,@backup_threshold = backup_threshold
                    ,@is_backup_alert_enabled = threshold_alert_enabled
                from msdb.dbo.log_shipping_monitor_primary with (nolock)
                where primary_server = upper(@primary_server)
                    and primary_database = @primary_database
            end -- processing on monitor server
            --
            -- insert primary details if available
            --
            if (@primary_id is not null)
            begin
                select @prev_primary_server = @primary_server
                        ,@prev_primary_database = @primary_database
                insert into #log_shipping_monitor (
                    status
                    ,is_primary
                    ,server
                    ,database_name
                    ,is_backup_alert_enabled)
                values (
                1
                    ,@primary_server
                    ,@primary_database
                    ,@backup_threshold
                    ,@is_backup_alert_enabled)
            end -- primary data available
        end -- process the primary
        --
        -- Insert the secondary
        --
        select @time_since_last_copy = datediff(minute, @last_copied_utc, @curutcdate)
                ,@time_since_last_restore = datediff(minute, @last_restored_utc, @curutcdate)
        insert into #log_shipping_monitor (
            status 
            ,is_primary 
            ,server  
            ,database_name 
            ,time_since_last_copy 
            ,last_copied_file 
            ,time_since_last_restore 
            ,last_restored_file 
            ,last_restored_latency 
            ,restore_threshold 
            ,is_restore_alert_enabled)
        values (
            case when (@time_since_last_restore > @restore_threshold or @last_restored_latency > @restore_threshold) then 1 else 0 end
            ,0
            ,@secondary_server
            ,@secondary_database
            ,@time_since_last_copy
            ,@last_copied_file
            ,@time_since_last_restore
            ,@last_restored_file
            ,@last_restored_latency
            ,@restore_threshold
            ,@is_restore_alert_enabled)
        --
        -- get the next secondary
        --
        fetch #hcsecondaries into @secondary_server, @secondary_database, @secondary_id,
                    @primary_server, @primary_database, @restore_threshold, @is_restore_alert_enabled, 
                    @last_copied_file, @last_copied_utc, @last_restored_file, @last_restored_utc, @last_restored_latency
    end -- while cursor for hcsecondaries
    close #hcsecondaries
    deallocate #hcsecondaries
    --
    -- return resultset
    --
SET @Body = RTRIM(@Body) + '<table cellspacing="0" cellpadding="0" width="100%">'

    WHILE EXISTS (SELECT TOP 1 idx FROM #log_shipping_monitor)
    BEGIN
        SELECT TOP 1 @idx = idx, @Status = (CASE WHEN status  = 0 THEN 'Good' ELSE 'Bad' END),
            @DB = database_name, @LastCopy = time_since_last_copy, @LastCFile =  last_copied_file,
            @LastRestore = time_since_last_restore, @LastRFile = last_restored_file,
            @Latency = last_restored_latency, @Threshold = restore_threshold 
        FROM #log_shipping_monitor      order by ts

        SET @Body = RTRIM(@Body) 
        + '<tr><td nowrap style="font-size:11px;font-family:Verdana">'
            + '<b>Database</b>:' + RTRIM(@DB) + '
'
            + CASE WHEN @Status = 'Good' THEN
                '<b>Status</b>:' + RTRIM(@Status) + '
'
                ELSE '<b>Status</b>:<font size="+1" color="red">' + RTRIM(@Status) + '</font>
' END           
            + '<b>Last Copied File</b>:' + RTRIM(@LastCFile) + '
'
            + '<b>Time since last Copy (min)</b>:' + RTRIM(@LastCopy) + '
'
            + '<b>Last Restored File</b>:' + RTRIM(@LastRFile) + '
'
            + '<b>Time since last Restore (min)</b>:' + RTRIM(@LastRestore) + '
'
            + '<b>Restore Latency (min)</b>:' + RTRIM(@Latency) + '
'
            + '<b>Restore Threshold (min)</b>:' + RTRIM(@Threshold) + '
'
            + '</td></tr>'
            + '<hr style="width: 100%; height: 1px;"> ' 
        DELETE FROM #log_shipping_monitor WHERE idx = @idx
    END

SET @Body = RTRIM(@Body) + '</table>'
        EXECUTE msdb.dbo.sp_send_dbmail @recipients = @Recipients 
                ,@subject = @Subject
                ,@profile_name = 'XYZ'
                ,@body = @Body
                ,@body_format = 'HTML'



end

DROP Table #log_shipping_monitor
DROP TABLE #primary_monitor
DROP TABLE #secondary_monitor