Log Shipping Email Alert

As a DBA, you can use the Log Shipping Status report in SSMS to check the Log Shipping is running without any issues. However, it is not possible to do 24/7 monitoring using SSMS manually. By the way, some third party monitoring systems can be overkill. So I create this email alert by myself, which can be executed as an Agent Job periodically, let's say per 15 minutes. This monitor bases on the last_restored_latency column value of the system table log_shipping_monitor_secondary in msdb system database. In order to send email content in HTML table format, a tailor-made stored procedure [ConvertTableToHtml] is being used.

/* JOB BODY START */
/* Specify your alert threshold in minutes here */
DECLARE @alertDelayMinutes int = 15;

DECLARE @NewLineChar char(2) = CHAR(13) + CHAR(10);

DECLARE @recipients varchar(max) = 'peter.lee@lazybugstudio.com;'
DECLARE @importance varchar(6) = 'Normal';
DECLARE @subject nvarchar(255) = N'DR Log Shipping Delay Check - ' + CONVERT(varchar(50), GETDATE(), 120);
DECLARE @body nvarchar(max);

CREATE TABLE #t (
    secondary_database sysname,
    last_copied_date datetime,
    last_restored_date datetime,
    last_restored_latency int,
    last_restored_ago int
);
INSERT #t
SELECT
    secondary_database,
    last_copied_date,
    last_restored_date,
    last_restored_latency,
    DATEDIFF(minute, last_restored_date, GETDATE())
FROM log_shipping_monitor_secondary;

DECLARE @htmlTable varchar(max);
EXEC master.dbo.ConvertTableToHtml 'SELECT * FROM #t', @htmlTable OUTPUT;;

SET @body = @htmltable;

DECLARE @cnt int = (SELECT COUNT(1) FROM #t WHERE last_restored_latency >= @alertDelayMinutes OR last_restored_ago >= @alertDelayMinutes);

IF @cnt > 0
BEGIN
--    SET @recipients = 'dbateam@lazybugstudio.com;';
    SET @importance = 'High';
    SET @subject = 'ERROR: ' + @subject;
    SET @body += N'<br/><b>DR DB Long Delay!</b>';
END

EXEC msdb.dbo.sp_send_dbmail @recipients = @recipients , @subject = @subject, @body = @body, @importance = @importance, @body_format = 'HTML';

DROP TABLE #t;
/* JOB BODY END */

/* ConvertTableToHtml Stored Proc. */
CREATE PROCEDURE [dbo].[ConvertTableToHtml](
      @SqlQuery AS NVARCHAR(4000),
      @Html AS VARCHAR(MAX) OUTPUT
)
AS

      DECLARE @Header AS NVARCHAR(MAX) = ''
      DECLARE @Column AS NVARCHAR(MAX) = ''
      DECLARE @Query AS NVARCHAR(MAX)
      DECLARE @Css AS VARCHAR(MAX) = '
            <style type="text/css">

            table.gridtable {
                font-family: verdana,arial,sans-serif;
                font-size:11px;
                color:#333333;
                border-width: 1px;
                border-color: #666666;
                border-collapse: collapse;
            }

            table.gridtable th {
                border-width: 1px;
                padding: 8px;
                border-style: solid;
                border-color: #666666;
                background-color: #dedede;
            }

            table.gridtable td {
                border-width: 1px;
                padding: 8px;
                border-style: solid;
                border-color: #666666;
                background-color: #ffffff;
            }

            </style>
            '
BEGIN

      SET @Query = 'SELECT * INTO ##columns FROM ( ' + @SqlQuery + ') Temp'
      EXECUTE(@Query)

      SELECT @Column = @Column + 'ISNULL(' + QUOTENAME(name) +' ,'' '')' + ' AS TD, '
      FROM tempdb.SYs.columns
      WHERE object_id = OBJECT_ID('tempdb..##columns')
     
      SET  @Column = LEFT(@Column,LEN(@Column)-1)

      SELECT @Header = @Header + '<TH>' +  name + '</TH>'
      FROM tempdb.SYs.columns
      WHERE object_id = OBJECT_ID('tempdb..##columns')
     
      SET @Header = '<TR>' + @Header  + '</TR>'
     
      SET @Query = 'SET  @Html = (SELECT ' + @Column + ' FROM ( ' + @SqlQuery + ') AS TR
       FOR XML AUTO ,ROOT(''TABLE''), ELEMENTS)'

      EXECUTE SP_EXECUTESQL @Query,N'@Html VARCHAR(MAX) OUTPUT',@Html OUTPUT
      SET  @Html = @Css + REPLACE(@Html,'<TABLE>' ,'<TABLE  class="gridtable">' + @Header)

      DROP TABLE ##columns

END

GO