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