As I have stated before I pride myself as being an accidental DBA.  So my T-SQL skills are not perfect.  One of the things that I do pride myself with is setting up a better way to monitor log shipping.

If you have any experience with log shipping, you know that the default monitoring tool that is setup as part of log shipping just tells you that something is wrong and no details at all.  So here you get emails every 5 minutes that state that something is wrong and you need to go dig trying to find the issue.  I hate that!

So I wrote a stored procedure that actually does the same thing, but gives me a chart that looks like the report on the monitoring server instead showing those databases where the log shipping is broken (in red none the less).  With slight tweaks you can set up another stored procedure to send you a daily report showing where your log shipping is every day.  Once you have the stored procedures working you just need to setup the jobs on your monitoring SQL server.

Remember this is a DR critical setup, you do not set it and forget it!!!

Here it is in it’s imperfection:

USE [dba_MaintenanceDB]
GO
CREATE PROCEDURE [dbo].[sp_LSAlert]
AS
    BEGIN
	SET NOCOUNT ON;
	DECLARE @MxCnt INT, @Cnt INT
	DECLARE @JobID VARBINARY(MAX)
	DECLARE @RunnableJobs INT
	DECLARE @Owner VARCHAR(20)
	DECLARE @vRecipients AS VARCHAR(MAX)
	DECLARE @vCopy_Recipients AS VARCHAR(MAX)
	DECLARE @vXML_String AS NVARCHAR(MAX)
	DECLARE @vBody AS NVARCHAR(MAX)
	DECLARE @vSQL_String AS NVARCHAR(MAX)
	DECLARE @vSubject AS NVARCHAR(255)
	DECLARE @UTCRunTime DATETIME = GETUTCDATE();
	SET @vRecipients = '	<list of recipients seperated by emi-colons>'
	SET @vCopy_Recipients = ''
	SET @vSubject = 'LogShipping Alert: ' + @@SERVERNAME

        IF OBJECT_ID('tempdb.dbo.#enum_job') IS NOT NULL
            BEGIN
                DROP TABLE dbo.#enum_job
            END
        IF OBJECT_ID('tempdb.dbo.#LS_Jobs') IS NOT NULL
            BEGIN
                DROP TABLE dbo.#LS_Jobs
            END

		INSERT INTO dbo.dba_LogShippingLatency
		        ( RunUTCDateTime
		        , Issue
		        , Primary_Server
		        , Primary_Database
		        , Secondary_Server
		        , Secondary_Database
		        , Last_Backup
		        , Backup_Threshold
		        , Last_Backup_LocalDate
		        , Last_Backup_File
		        , Last_Copied_LocalDate
		        , Last_Copied_File
		        , Restore_Threshold
		        , Last_Restore
		        , Last_Restore_LocalDate
		        , Last_Restore_File
		        , Last_Restore_Latency )

        SELECT
			@UTCRunTime AS RunUTCDateTime
            , CASE WHEN [sec].restore_threshold < DATEDIFF(MINUTE,
                                                             [sec].last_restored_date,
                                                             GETDATE()) OR
                          [sec].restore_threshold <= [sec].last_restored_latency OR
                          [pri].last_backup_date < DATEDIFF(MINUTE,
                                                            [pri].last_backup_date,
                                                            GETDATE())
                     THEN 'Issue'
                     ELSE ''
                END AS [Issue]
              , [pri].primary_server
              , [pri].primary_database
              , [sec].secondary_server
              , [sec].secondary_database
              , DATEDIFF(MINUTE, [pri].last_backup_date, GETDATE()) AS Last_Backup
              , [pri].backup_threshold
              , [pri].last_backup_date
              , CASE WHEN [pri].last_backup_file LIKE '\\<part of primary location name>%'
                     THEN REPLACE([pri].last_backup_file,
                                  '<primary file location>\' +
                                  [pri].primary_server + '\' +
                                  [pri].primary_database + '\Log\', '')
                     ELSE REPLACE([pri].last_backup_file,
                                  '<secondary file location>\' +
                                  [pri].primary_server + '\' +
                                  [pri].primary_database + '\Log\', '')
                END AS Last_Backup_File
              , CASE WHEN [sec].last_copied_date IS NULL THEN ''
                     ELSE CAST([sec].last_copied_date AS VARCHAR(50))
                END AS Last_Copied_Date
              , CASE WHEN [sec].last_copied_file IS NULL THEN ''
                     ELSE REPLACE([sec].last_copied_file,
                                  '<secondary file location>' +
                                  [pri].primary_server + '\' +
                                  [pri].primary_database + '\Log\', '')
                END AS Last_Copied_File
              , [sec].restore_threshold
              , DATEDIFF(MINUTE, [sec].last_restored_date, GETDATE()) AS Last_Restore
              , [sec].last_restored_date
              , CASE WHEN [sec].last_restored_file IS NULL THEN ''
                     ELSE REPLACE([sec].last_restored_file,
                                  '<secondary file location>' +
                                  [sec].secondary_server + '\' +
                                  [sec].secondary_database + '\Log\', '')
                END AS last_restored_file
              , [sec].last_restored_latency
            FROM
                msdb.[dbo].[log_shipping_monitor_primary] [pri]
                INNER JOIN msdb.[dbo].[log_shipping_monitor_secondary] [sec]
                    ON [pri].primary_database = [sec].primary_database AND
                       [pri].primary_server = [sec].primary_server
            WHERE
                [sec].restore_threshold < DATEDIFF(MINUTE,
                                                   [sec].last_restored_date,
                                                   GETDATE()) OR
                [sec].restore_threshold <= [sec].last_restored_latency OR
                [pri].last_backup_date < DATEDIFF(MINUTE,
                                                  [pri].last_backup_date,
                                                  GETDATE())

/****************************************************************
Setup Email body
Note: Because we get a lot of transactions around 5am to 8am, we allow our production latency to get to 120 minutes.
****************************************************************/
        IF (
             SELECT COUNT (*)
				FROM dbo.dba_LogShippingLatency AS dLSL
				WHERE
					dLSL.RunUTCDateTime = @UTCRunTime
					AND dLSL.Issue <> ''
					AND (
							(
								dLSL.Primary_Database = '<Server_Name>'
								AND CAST(dLSL.RunUTCDateTime AS TIME) BETWEEN '10:00:00' AND '13:00:00' -- between 5am and 8am
								AND dLSL.Last_Restore_Latency > 120
							)
						OR
                        	(
								dLSL.Primary_Database = '<Server_Name>'
								AND NOT (CAST(dLSL.RunUTCDateTime AS TIME) BETWEEN '10:00:00' AND '13:00:00') -- NOT between 5am and 8am
								AND dLSL.Last_Restore_Latency > dLSL.Restore_Threshold
							)
						OR
							(
								dLSL.Primary_Database <> '<Server_Name>'
								AND dLSL.Last_Restore_Latency > dLSL.Restore_Threshold
							)
						)

           ) > 0
            BEGIN;
			        SET @vXML_String = CONVERT (NVARCHAR(MAX), (
			 SELECT
					''
				  , dLSL.Issue AS 'td'
				  , ''
				  , dLSL.Primary_Server AS 'td'
				  , ''
				  , dLSL.Primary_Database AS 'td'
				  , ''
				  , dLSL.Secondary_Server AS 'td'
				  , ''
				  , dLSL.Secondary_Database AS 'td'
				  , ''
				  , dLSL.Last_Backup AS 'td'
				  , ''
				  , dLSL.Backup_Threshold AS 'td'
				  , ''
				  , dLSL.Last_Backup_LocalDate AS 'td'
				  , ''
				  , dLSL.last_copied_Localdate AS 'td'
				  , ''
				  , dLSL.Last_Copied_File AS 'td'
				  , ''
				  , dLSL.restore_threshold AS 'td'
				  , ''
				  , dLSL.Last_Restore AS 'td'
				  , ''
				  , dLSL.last_restore_Localdate AS 'td'
				  , ''
				  , dLSL.Last_Restore_File AS 'td'
				  , ''
				  , dLSL.last_restore_latency AS 'td'
				FROM
					dbo.dba_LogShippingLatency AS dLSL
				WHERE
					dLSL.RunUTCDateTime = @UTCRunTime
				ORDER BY
					dLSL.primary_server
				  , dLSL.primary_database
				  , dLSL.Secondary_Server
				  , dLSL.Secondary_Database
		   FOR
			 XML PATH('tr')
		   ))
        SELECT
                @vXML_String
        SET @vBody = '
<h3><center>LogShipping Alerts</center></h3>
<center>
<table border=1 cellpadding=2>
<tr>
<th>Issue</th>
<th>Primary_Server</th>
<th>Primary_Database</th>
<th>Secondary_Server</th>
<th>Secondary_Database</th>
<th>Last_Backup</th>
<th>Backup_Threshold</th>
<th>Last_Backup_Date</th>
<th>Last_Copied_Date</th>
<th>Last_Copied_File</th>
<th>Restore_Threshold</th>
<th>Last_Restore</th>
<th>Last_Restore_Date</th>
<th>Last_Restore_File</th>
<th>Last_Restore_Latency</th>
</tr>
'
        SET @vBody = @vBody + @vXML_String + '</table>
</center>'

        IF OBJECT_ID('tempdb.dbo.#enum_job') IS NOT NULL
            BEGIN
                DROP TABLE dbo.#enum_job
            END
        IF OBJECT_ID('tempdb.dbo.#LS_Jobs') IS NOT NULL
            BEGIN
                DROP TABLE dbo.#LS_Jobs
            END
----------------------------------------
--	Variable Update: Finalize @vBody Variable Contents
----------------------------------------
        SET @vBody = '
		<html>
			<body>
<style type="text/css">
				table {font-size:8.0pt;font-family:Arial;text-align:left;}
				tr {text-align:left;}
			</style>

	' + @vBody + '
			</body>
		</html>
	'
        SET @vBody = REPLACE(@vBody, '
<tr>
<td>Issue</td>
',
                             '
<tr bgcolor="IndianRed">
<td align="center">Issue</td>
')
        SET @vBody = REPLACE(@vBody, '
<td>right_align', '
<td align="right">')
        SET @vBody = REPLACE(@vBody, '
<td>center_align', '
<td align="center">')
----------------------------------------
--	sp_send_dbmail: Deliver Results / Notification To End User(s)
----------------------------------------
        EXEC msdb.dbo.sp_send_dbmail
	--@profile_name =  'SystemEmail',
            @recipients = @vRecipients
          , @importance = 'High'
          , @copy_recipients = @vCopy_Recipients
          , @subject = @vSubject
          , @body = @vBody
          , @body_format = 'HTML'
       END;
    END
GO

With tweaks and modifications you should be able to get this working for you as well.  🙂

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s