Alerting for Database Maintenance

routermonkey

I'm trying to create an alert that will tell me if nightly database maintenance completed successfully or failed and how long from start to completion the maintenance job took.

I can find the events in Orion.Events, but not quite sure how to incorporate this into an alert.  Trying to use Custom SQWL Event (Advanced) type of alert, but not quite sure of which of the available SWQL Conditions that will work.

Using this SWQL can give me the events in a table, but I'm looking how to use this in an alert.

SELECT
 TOLOCAL(EventTime) AS LocalEventTime,
 Message,
 EventType
FROM Orion.Events
WHERE Message LIKE '%Starting Nightly Maintenance%'
OR Message LIKE '%Nightly maintenance failed%'
OR Message LIKE '%Nightly Maintenance Completed%'
ORDER BY EventTime DESC

Lofstrand

Hi there

Do you want a "notification" each morning on the maintenance status? In that case you probably want to create a scheduled report instead.

If you want an ALERT when MT fails, then it's an alert but we can't look at all values. We just want tonights values. (I can't find Custom SWQL alert against events, so I choose Polling Engine instead):

-- Adjustable part
INNER JOIN
(
-- Trigger if Maintenance job failed tonigh
SELECT
    E.EngineID
FROM Orion.Events AS E
WHERE 
    E.Message LIKE '%Nightly maintenance failed%'
    AND E.EventTime>ADDHOUR(-20,GETUTCDATE())  -- Only look on tonights jobs
    AND HOUR(ToLocal(GETUTCDATE())) BETWEEN 2 AND 10  -- Set between what hours the job should be completed

UNION 
( -- Trigger if Maintenance job completed message is NOT found, meaning it's stuck or took a long time
SELECT
    CASE  -- Only trigger if MT Completed is NOT found
        WHEN E.EventTime IS NULL THEN E.EngineID
    END AS EngineID
FROM Orion.Events AS E
WHERE 
    E.Message LIKE '%Nightly Maintenance Completed%'
    AND E.EventTime>ADDHOUR(-10,GETUTCDATE())  -- Only look on tonights jobs
    AND HOUR(ToLocal(GETUTCDATE())) BETWEEN 2 AND 4  -- Set between what hours the job should be completed
)
) AS T ON Engines.EngineID=T.EngineID

Looks like this:

Then to get the event messages in the alert message, I had to create a Custom SQL variable as I couldn't find event messages as variable. We also need to insert the variable as one string but we want them on separate lines for easy reading. Therefor the extra code:

SELECT REPLACE(REPLACE(
(SELECT
	CONVERT(varchar(40),E.EventTime) + ' ' +E.Message + ''
FROM Events AS E
WHERE
	E.EventType IN (5100,5101,5102)
	AND E.EventTime>DATEADD(HOUR,-10,GETDATE())
ORDER BY
	E.EventTime
FOR XML Path ('')
), '<', '<'), '>', '>')

Above is the SQL code that should be used when inserting a "Custom SQL variable". Looks like this then:

Hope it's what you where after.