SQL Server Log Shipping Monitoring and Email Notification

By:   |   Updated: 2016-10-07   |   Comments (7)   |   Related: > Log Shipping


Problem

We have a few reporting servers where we have SQL Server Log Shipping setup and we are responsible for making sure the data is in sync for reporting users to generate reports. If there is an issue we need to be notified in order to fix the issue ASAP.  In this tip I will demonstrate how we can automate SQL Server Log Shipping monitoring and keep the stakeholders informed.

Solution

Let's jump right in and walk through how to setup SQL Server Log Shipping monitoring.

Setup SQL Server Linked Servers for Log Shipping Monitoring

Step 1

Create a Linked Server to each server which is setup for Log Shipping on your monitoring server and ensure you test each Linked Server.

Setup SQL Server Table for Log Shipping Monitoring

Step 2

Create a table for centralized data and monitoring.

CREATE TABLE [dbo].[LSServers](
[SN] [int] IDENTITY(1,1) NOT NULL,
[PRIMARY_SERVER] [sysname] NULL,
[SECONDARY_SERVER] [sysname] NULL,
[SQLVersion] [varchar](20) NULL
) ON [PRIMARY]

GO

Insert Records into the SQL Server Log Shipping Monitoring Table

Step 3: Insert records of all your PRIMARY and SECONDARY servers in the monitoring table as shown below:

Sample SQL Server Log Shipping Records

SQL Server Stored Procedure to Capture Log Shipping Status

Step 4: Create the below stored procedure on your monitoring server to pull the SQL Server Log Shipping status from the respective primary and secondary servers based on the SQL Server version.  At a high level, this code captures the Log Shipping status based on the SQL Server version from the corresponding system tables in the MSDB database then inserts the data into the centralized monitoring table created in step 2.  Then the status for each record in the monitoring table is updated.  The code finishes with sending a status email.

USE [DBA]
GO
CREATE PROCEDURE [dbo].[usp_GetLogShippingStatus]
@mode BIT = 0 
AS
BEGIN
SET NOCOUNT ON
SET XACT_ABORT ON


DECLARE @Recipients VARCHAR(275)
DECLARE @MailSubject VARCHAR(275) 
DECLARE @Xml VARCHAR(MAX)
DECLARE @Mailtext VARCHAR(MAX)
DECLARE @Server VARCHAR(25) 
DECLARE @Curdate DATETIME
DECLARE @MailString VARCHAR(MAX)
DECLARE @Note NVARCHAR(1000)
DECLARE @Filedate DATETIME
DECLARE @Dbname SYSNAME
DECLARE @Latency SYSNAME
DECLARE @Filename NVARCHAR (500)
DECLARE @Tempname NVARCHAR (500)
DECLARE @Primary SYSNAME
DECLARE @Secondary SYSNAME
DECLARE @SQLVersion VARCHAR(20)
DECLARE @Sql VARCHAR(500)

SET @Curdate = GETDATE()
SET @Server = @@SERVERNAME 
SET @Recipients = '[email protected]'

IF OBJECT_ID('tempdb..#TABLE_LS_MONITOR') IS NOT NULL 
DROP TABLE #TABLE_LS_MONITOR

CREATE TABLE #TABLE_LS_MONITOR
(
SN INT IDENTITY(1,1)
,PRIMARY_SERVER SYSNAME NULL
,PRIMARY_DATABASE SYSNAME NULL
,SECONDARY_SERVER SYSNAME NULL
,SECONDARY_DATABASE SYSNAME NULL
,LSSTATUS VARCHAR(10)
,LAST_BACKUP_FILE NVARCHAR(500) NULL
,BACKUP_THRESHOLD INT NULL
,LAST_BACKUP_TIME DATETIME
,TIME_SINCE_LAST_BACKUP INT NULL
,LAST_RESTORED_FILE NVARCHAR(500) NULL
,RESTORE_THRESHOLD INT NULL
,LAST_RESTORE_TIME DATETIME
,TIME_SINCE_LAST_RESTORE INT NULL
,LAST_RESTORED_LATENCY INT NULL
)

DECLARE LSServers CURSOR FOR
SELECT PRIMARY_SERVER, SECONDARY_SERVER, SQLVersion FROM LSServers;

OPEN LSServers

FETCH NEXT FROM LSServers
INTO @Primary, @Secondary, @SQLVersion

WHILE @@FETCH_STATUS = 0
BEGIN

IF @SQLVersion in ('SQL2016','SQL2014','SQL2012','SQL2008','SQL2008R2','SQL2005')
BEGIN

SET @Sql = 'SELECT p.primary_server,p.primary_database, s.secondary_server, 
s.secondary_database, p.last_backup_file, p.backup_threshold, 
p.last_backup_date, s.last_restored_file, s.restore_threshold, s.last_restored_date,
s.last_restored_latency
FROM ' + @Primary + '.msdb.dbo.log_shipping_monitor_primary p INNER JOIN ' 
+ @Secondary + '.msdb.dbo.log_shipping_monitor_secondary s
ON p.primary_server = s.primary_server and p.primary_database = s.primary_database'

INSERT INTO #TABLE_LS_MONITOR (PRIMARY_SERVER, PRIMARY_DATABASE, SECONDARY_SERVER,
SECONDARY_DATABASE, LAST_BACKUP_FILE, BACKUP_THRESHOLD, LAST_BACKUP_TIME, 
LAST_RESTORED_FILE, RESTORE_THRESHOLD, LAST_RESTORE_TIME, LAST_RESTORED_LATENCY)
EXEC(@Sql)

END

IF @SQLVersion = 'SQL2000'
BEGIN

SET @Sql = 'SELECT p.primary_server_name,p.primary_database_name, 
s.secondary_server_name, s.secondary_database_name, p.last_backup_filename, 
p.backup_threshold, p.last_updated, s.last_loaded_filename, s.out_of_sync_threshold,
s.last_loaded_last_updated
FROM ' + @Primary + '.msdb.dbo.log_shipping_primaries p INNER JOIN ' 
+ @Primary + '.msdb.dbo.log_shipping_secondaries s
ON p.primary_id = s.primary_id'

INSERT INTO #TABLE_LS_MONITOR (PRIMARY_SERVER, PRIMARY_DATABASE, SECONDARY_SERVER, 
SECONDARY_DATABASE, LAST_BACKUP_FILE, BACKUP_THRESHOLD, 
LAST_BACKUP_TIME, LAST_RESTORED_FILE, RESTORE_THRESHOLD, LAST_RESTORE_TIME)
EXEC(@Sql)

END



FETCH NEXT FROM LSServers
INTO @Primary, @Secondary, @SQLVersion
END

CLOSE LSServers;
DEALLOCATE LSServers; 


UPDATE #TABLE_LS_MONITOR SET TIME_SINCE_LAST_BACKUP = DATEDIFF(mi, LAST_BACKUP_TIME, 
@Curdate), TIME_SINCE_LAST_RESTORE = DATEDIFF(mi, LAST_RESTORE_TIME, @Curdate)

UPDATE #TABLE_LS_MONITOR SET LAST_BACKUP_FILE = SUBSTRING(LAST_BACKUP_FILE, 
LEN(LAST_BACKUP_FILE) - CHARINDEX('\', REVERSE(LAST_BACKUP_FILE))+2, LEN(LAST_BACKUP_FILE)),
LAST_RESTORED_FILE = SUBSTRING(LAST_RESTORED_FILE, LEN(LAST_RESTORED_FILE) - CHARINDEX('\',
REVERSE(LAST_RESTORED_FILE))+2, LEN(LAST_RESTORED_FILE))
WHERE CHARINDEX(N'\',LAST_BACKUP_FILE)!=0 OR CHARINDEX(N'\',LAST_RESTORED_FILE)!=0 

DECLARE LSCURSOR CURSOR FOR
SELECT PRIMARY_DATABASE, LAST_RESTORED_FILE
FROM #TABLE_LS_MONITOR WHERE LAST_RESTORED_LATENCY IS NULL;

OPEN LSCURSOR

FETCH NEXT FROM LSCURSOR
INTO @Dbname, @Filename

WHILE @@FETCH_STATUS = 0
BEGIN

SELECT @Tempname = RIGHT (@Filename, LEN (@Filename) - (LEN(@Dbname) + LEN ('_tlog_')))
IF (CHARINDEX ('.',@Tempname,0) > 0)
SELECT @Tempname = LEFT (@Tempname, CHARINDEX ('.',@Tempname,0) - 1)
SELECT @Filedate = CONVERT (DATETIME,SUBSTRING (@Tempname, 1,8),112)
IF (LEN (@Tempname) = 12)
BEGIN
SELECT @Filedate = DATEADD (hh, CONVERT (INT, SUBSTRING (@Tempname,9,2)),@Filedate)
SELECT @Filedate = DATEADD (mi, CONVERT (INT, SUBSTRING (@Tempname,11,2)),@Filedate)
END

UPDATE #TABLE_LS_MONITOR SET LAST_RESTORED_LATENCY = datediff(mi, @Filedate,
LAST_RESTORE_TIME) WHERE LAST_RESTORED_LATENCY IS NULL

FETCH NEXT FROM LSCURSOR
INTO @Dbname, @Filename
END

CLOSE LSCURSOR;
DEALLOCATE LSCURSOR;



UPDATE #TABLE_LS_MONITOR SET LSSTATUS = CASE 
WHEN TIME_SINCE_LAST_BACKUP > BACKUP_THRESHOLD THEN 'BAD'
WHEN TIME_SINCE_LAST_RESTORE > RESTORE_THRESHOLD THEN 'BAD'
WHEN LAST_RESTORED_LATENCY > RESTORE_THRESHOLD THEN 'BAD'
ELSE 'GOOD' END

SET @Mailtext ='<html>
<style type="text/css">
table.gridtable {
font-family: verdana,arial,sans-serif;
font-size:12px;
color:#000000;
border-width: 1px;
border-color: #666666;
border-collapse: collapse;
}
table.gridtable th {
border-width: 1px;
font-size:11px;
border-style: solid;
border-color: #666666;
}
table.gridtable td {
border-width: 1px;
font-size:11px;
border-style: solid;
border-color: #666666;
}
</style>
<body>
<table class="gridtable"> 
<tr bgcolor = ''''#808080''''>
<th> Primary_Server </th> <th> Primary_DB </th> <th> Secondary_Server </th> 
<th> Secondary_DB </th> <th> Status </th> <th> Last Backup File</th> 
<th> Backup Threshold </th> <th> TimeSince LastBackup </th> 
<th> Last Restored File </th> <th> Restore Threshold </th> 
<th> TimeSince LastRestore </th> <th> LastRestored Latency</th> 
</tr>' 

SET @Note = '<b>Note:</b><br/>
Time unit is minute for all of the time measures used here.<br/>
</body></html>' 

IF @mode = 0
BEGIN

SET @Xml = CAST(( SELECT 'tr/@bgcolor'= CASE LSSTATUS WHEN 'GOOD' 
THEN '#90EE90' 
ELSE '#FF4500' END, 
td = PRIMARY_SERVER, '', td = PRIMARY_DATABASE , '', 
td = SECONDARY_SERVER, '',
td = SECONDARY_DATABASE, '', 
td = LSSTATUS, '', td = LAST_BACKUP_FILE, '', td = BACKUP_THRESHOLD, '', 
td = TIME_SINCE_LAST_BACKUP, '', 
td = LAST_RESTORED_FILE, '', td = RESTORE_THRESHOLD, '', 
td = TIME_SINCE_LAST_RESTORE, '',
td = LAST_RESTORED_LATENCY, ''
FROM #TABLE_LS_MONITOR 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) 

SET @Mailtext = @Mailtext + @Xml +'</table><br/>' + @Note 

SET @MailSubject = 'Transaction Log Shipping Status' 
SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name = ''sqldbmail'', 
@recipients = ''' + @Recipients + ''',@subject = ''' + @MailSubject + ''', 
@body = ''' + @Mailtext + ''', @body_format = ''HTML'' '
EXEC (@MailString)
END
ELSE 
BEGIN

IF EXISTS(SELECT 1 FROM #TABLE_LS_MONITOR WHERE LSSTATUS = 'BAD')
BEGIN

SET @Xml = CAST(( SELECT 'tr/@bgcolor'= CASE LSSTATUS WHEN 'GOOD' 
THEN '#90EE90' ELSE '#FF4500' END, td = PRIMARY_SERVER, '', td = PRIMARY_DATABASE , '', 
td = SECONDARY_SERVER, '', td = SECONDARY_DATABASE, '', 
td = LSSTATUS, '', td = LAST_BACKUP_FILE, '', td = BACKUP_THRESHOLD, '', 
td = TIME_SINCE_LAST_BACKUP, '', td = LAST_RESTORED_FILE, '', td = RESTORE_THRESHOLD, '', 
td = TIME_SINCE_LAST_RESTORE, '', td = LAST_RESTORED_LATENCY, ''
FROM #TABLE_LS_MONITOR WHERE LSSTATUS = 'BAD' 
FOR XML PATH('tr'), ELEMENTS ) AS NVARCHAR(MAX)) 

SET @Mailtext = @Mailtext + @Xml +'</table><br/>' + @Note

SET @MailSubject = 'Transaction Log Shipping is out of sync' 

SET @MailString = 'SQLDBAExperts.msdb.dbo.sp_send_dbmail @profile_name = ''sqldbmail'', 
@recipients = ''' + @Recipients + ''', @subject = ''' + @MailSubject + ''', @body = ''' 
+ @Mailtext + ''', @body_format = ''HTML'' '
EXEC (@MailString)
END

END

DROP TABLE #TABLE_LS_MONITOR

END
GO

SQL Server Agent Job for Log Shipping Notification

Step 5: Create a SQL Server Agent Job for sending Log Shipping notifications to all stakeholders.

USE [msdb]
GO

/****** Object: Job [Monitor Logshipping - Shiftwise] 
Script Date: 04/22/2016 03:53:50 ******/
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT @ReturnCode = 0
/****** Object: JobCategory [2 - DBA - On-Demand] Script Date: 04/22/2016 03:53:50 ******/

IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE 
name=N'2 - DBA - On-Demand' AND category_class=1)
BEGIN
   EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', 
   @name=N'2 - DBA - On-Demand'

   IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
END

DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name= 
N'Monitor Logshipping - Shiftwise', 
@enabled=1, 
@notify_level_eventlog=0, 
@notify_level_email=0, 
@notify_level_netsend=0, 
@notify_level_page=0, 
@delete_level=0, 
@description=N'This job will send Logshipping status report to DBA team 
for all the databases configured.', 
@category_name=N'2 - DBA - On-Demand', 
@owner_login_name=N'sa', @job_id = @jobId OUTPUT
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
/****** Object: Step [Logshipping status] Script Date: 04/22/2016 03:53:50 ******/
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'Logshipping status', 
@step_id=1, 
@cmdexec_success_code=0, 
@on_success_action=1, 
@on_success_step_id=0, 
@on_fail_action=2, 
@on_fail_step_id=0, 
@retry_attempts=0, 
@retry_interval=0, 
@os_run_priority=0, @subsystem=N'TSQL', 
@command=N'exec usp_GetLogshippingStatus 0', 
@database_name=N'DBA', 
@flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id=@jobId, 
@name=N'Logshipping Status Schedule', 
@enabled=1, 
@freq_type=4, 
@freq_interval=1, 
@freq_subday_type=8, 
@freq_subday_interval=8, 
@freq_relative_interval=0, 
@freq_recurrence_factor=0, 
@active_start_date=20120920, 
@active_end_date=99991231, 
@active_start_time=0, 
@active_end_time=235959, 
@schedule_uid=N'a4b8a45f-5b47-40f1-86e6-3a55dc395052'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION
EndSave:
GO

Review the SQL Server Agent Job in Management Studio

Step 6: Refer to the below screen in SQL Server Management Studio to review the General properties for the job.

Review the SQL Server Agent Job in Management Studio

Review the Job Step to call the Log Shipping monitoring stored procedure.

Review the Job Step to call the Log Shipping monitoring stored procedure.

SQL Server Agent Job Step details.

SQL Server Agent Job Step details.

Schedule the SQL Server Agent Job as per your requirements.  In this example, the schedule is every 8 hours for 24x7 coverage.

Schedule the SQL Server Agent Job as per your requirements.

SQL Server Agent Log Shipping Monitoring Email

Step 6: With your SQL Server Log Shipping monitoring in place, you will receive email notifications similar to the below screen shot.  Keep in mind if any Log Shipping configuration has issues the email will highlight that row in RED.

SQL Server Agent Log Shipping Monitoring Email
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Atul Gaikwad Atul Gaikwad has over 14+ years of experience with SQL Server. He currently works for one of the leading MNCs in Pune as a Delivery Manager.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2016-10-07

Comments For This Article




Monday, May 30, 2022 - 7:30:38 AM - jagadeesh Back To Top (90119)
USE [msdb]
GO

/****** Object: View [dbo].[JobStatus_LS_Maintenance] Script Date: 5/24/2022 2:39:29 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE view [dbo].[JobStatus_LS_Maintenance] as
SELECT
--[sJOB].[job_id] AS [JobID],
[sJOB].[name] AS [JobName]
, CASE
when [sJOBH].[run_date] IS NULL then 'Run History unavailable/deleted by a cleanup job'
WHEN [sJOBH].[run_date] < CAST (DATEADD(HH, -12, GETDATE()) as int)
THEN 'Did not run in 12 hours'

ELSE CAST(
stuff (
stuff (CAST([sJOBH].[run_date] AS CHAR(8)), 5, 0, '/'), 8, 0, '/')
+ ' '
+ STUFF(
STUFF(RIGHT('000000' + CAST([sJOBH].[run_time] AS VARCHAR(6)), 6)
, 3, 0, ':')
, 6, 0, ':')
AS varchar)
END AS [LastRunDateTime]
, Case
when [sJOBH].[run_date] IS NULL then 'N/A'
WHEN [sJOBH].[run_date] < CAST (DATEADD(HH, -12, GETDATE()) as int) then 'N/A'

when [sJOBH].[run_status] = 0 then 'failed'
when [sJOBH].[run_status] = 1 THEN 'Succeeded'
when [sJOBH].[run_status] = 2 THEN 'Failed'
when [sJOBH].[run_status] = 3 THEN 'Failed (Canceled)'
when [sJOBH].[run_status] = 4 THEN 'Running'
--WHEN 0 THEN 'Failed'
--WHEN 1 THEN 'Succeeded'
--WHEN 2 THEN 'Failed'
--WHEN 3 THEN 'Failed (Canceled)'
--WHEN 4 THEN 'Running' -- In Progress
END
AS [LastRunStatus]
FROM
[msdb].[dbo].[sysjobs] AS [sJOB]
LEFT JOIN (
SELECT
[job_id]
, MIN([next_run_date]) AS [NextRunDate]
, MIN([next_run_time]) AS [NextRunTime]
FROM [msdb].[dbo].[sysjobschedules]
GROUP BY [job_id]
) AS [sJOBSCH]
ON [sJOB].[job_id] = [sJOBSCH].[job_id]
LEFT JOIN (
SELECT
[job_id]
, [run_date]
, [run_time]
, [run_status]
, [run_duration]
, [message]
, ROW_NUMBER() OVER (
PARTITION BY [job_id]
ORDER BY [run_date] DESC, [run_time] DESC
) AS RowNumber
FROM [msdb].[dbo].[sysjobhistory]
WHERE [step_id] = 0
) AS [sJOBH]
ON [sJOB].[job_id] = [sJOBH].[job_id]
AND [sJOBH].[RowNumber] = 1
WHERE [sJOB].[name] = 'MaintenancePlan-CHECKDB.Subplan_1'
or [sJOB].[name] = 'MaintenancePlan-DiffBackup.Subplan_1'
or [sJOB].[name] = 'MaintenancePlan-FullBackup.Subplan_1'
or [sJOB].[name] = 'MaintenancePlan-IndexRebuild.Subplan_1'
or [sJOB].[name] = 'MaintenancePlan-SystemDB BackUP.Subplan_1'
or [sJOB].[name] = 'MaintenancePlan-Tlog.Subplan_1'
or [sJOB].[name] = 'MaintenancePlan-UpdateStatistics.Subplan_1'

GO

Tuesday, April 21, 2020 - 4:05:03 AM - eric Back To Top (85429)

thank for ypur script, so, mail never started, i can't receive anything

Wednesday, November 6, 2019 - 2:45:40 AM - Venkhatesh Back To Top (82992)

hi .. good post about alert.. can we implement this without linked server ?

Thursday, February 8, 2018 - 9:33:05 AM - Mohayyan Back To Top (75137)

Hi,

This script works well but it is failing to provide Valid result for log shipping configured with Witness server as you know when you configured with witness server, the Valid status will be captured from witness server only. Also it is failing for the servers having different collation settings for Tempdb database as per below error.

Executed as user: NT SERVICE\SQLSERVERAGENT. Incorrect syntax near '\'. [SQLSTATE 42000] (Error 102)  Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS_KS_WS" in the equal to 

 

operation. [SQLSTATE 42000] (Error 468).  The step failed.

Tuesday, November 7, 2017 - 9:57:49 AM - Jerome Goldsberg Back To Top (69358)

Hi,

Please I need help, the above script isn't working for me. there only modification i did to the above script was to replace the email address to mine.

Below is the error message am getting.

 

Message

Executed as user: ***********\SQLUAT-02$. Incorrect syntax near '-'. [SQLSTATE 42000] (Error 102).  The step failed.

 

When i execute just the SP I get same error (Incorrect syntax near '-'.)

 

I will appreciate to get feedback, thanks 

Thursday, September 14, 2017 - 1:34:21 PM - Dhanapal Palanisamy Back To Top (66287)

Hi,

 

Thanks for the script. its working fine for me. But just i wanted to know calculation on  below commant 

 

UPDATE #TABLE_LS_MONITOR SET LSSTATUS = CASE 
WHEN TIME_SINCE_LAST_BACKUP > BACKUP_THRESHOLD THEN 'BAD'
WHEN TIME_SINCE_LAST_RESTORE > RESTORE_THRESHOLD THEN 'BAD'
WHEN LAST_RESTORED_LATENCY > RESTORE_THRESHOLD THEN 'BAD'
ELSE 'GOOD' END

 

 

Monday, July 24, 2017 - 11:36:38 AM - Saravanan N K Back To Top (63172)

Hi Atul,

We have created the SP and linked servers as mentioned in the article. But the SP is scanning all the servers in LSServers list. We have added 4 servers to the LSServers list, but it is scanning only the first server. It seems that the cursor is not working properly.

Please help.

 














get free sql tips
agree to terms