This script helps in monitoring logshipping status of secondary databases and how far behind they are their respective primaries.
You can have this run as a scheduled job to monitor the "Minutes_Behind_Primary" parameter.
/*Creat temporary table to hold the values */
IF OBJECT_ID('TEMPDB.dbo.#Logshipping_Monitor') IS NOT NULL
DROP TABLE #Logshipping_Monitor
CREATE TABLE #Logshipping_Monitor
( Primary_Server nvarchar(100),
Primary_Database nvarchar(100),
Secondary_Server nvarchar(100),
Secondary_Database nvarchar(100),
Restore_Latency int,
Min_Behind_Primary int
)
/* Insert temp table with values */
INSERT INTO #Logshipping_Monitor
SELECT secondary_server, secondary_database, primary_server, primary_database,
last_restored_latency,
DATEDIFF(minute, last_restored_date_utc, GETUTCDATE()) + last_restored_latency [Minutes Behind Current Time]
FROM msdb.dbo.log_shipping_monitor_secondary
ORDER BY [Minutes Behind Current Time] desc
/*Send email alert only if Secondary is behind Primary by 60min */
--Set the body of the email
DECLARE @xml nvarchar(max)
DECLARE @body nvarchar(Max)
SET @xml = CAST((SELECT [Primary_Server] AS 'td','', [Primary_Database] AS 'td','', [Secondary_Server] AS 'td','', [Secondary_Database] AS 'td','',
[Restore_Latency] AS 'td','',
Min_Behind_Primary AS 'td'
FROM #Logshipping_Monitor
WHERE Min_Behind_Primary > 60
ORDER BY [Min_Behind_Primary] Desc
FOR XML PATH('tr'), ELEMENTS) AS NVARCHAR(MAX))
SET @body = '<html><body><H3>Logshipping Report</H3>
<table border = 1>
<tr>
<th>Primary_Server</th>
<th>Primary_Database</th>
<th>Secondary_Server</th>
<th>Secondary_Database</th>
<th>Latency_Min</th>
<th>Min_Behind_Primary</th>'
SET @body = @body + @xml +'</table></body></html>'
--Send email
EXEC msdb.dbo.sp_send_dbmail
@profile_name = '<DatabaseMailProfile>',
@Subject = 'Logshipping Monitoring',
@recipients = 'Recipient Emails',
@body = @body,
@body_format = 'HTML'
DROP TABLE #Logshipping_Monitor
This article describes how to add a log shipping monitor to a log shipping configuration that is already in place.
2012-03-15
6,333 reads
This article describes the requirements to log ship databases between workgroup computers
2012-01-30
4,667 reads
In this article you will see the detailed steps needed to implement the log shipping for large databases.
2011-09-23
2,889 reads
You just had a great weekend. You go grab your coffee and before you take a sip a manager grabs you and says, "we need your help." We need to migrate a very large database to our new data center. We need to keep the data in sync and have a short period of downtime when we cutover.
2010-08-05
3,477 reads
Longtime author Leo Peysakhovich has implemented a log shipping mechanism that can recover from failures and give you control over how it works. Read on if you want to implement your own version of log shipping and have control over all aspects of the process.
2010-07-01
5,651 reads