Is it possible to monitor the completion of DB maintenance?

We've been having issues with DB maintenance lately after we moved our SQL server to a cluster. We've been manually checking the log file located at C:\ProgramData\Solarwinds\Logs\Orion\swdebugMaintenance.log to make sure it's completed, but i'm wondering if there's a better way to do this, perhaps with SAM. I was thinking of just creating a custom powershell monitor and checking if "Database Maintenance Complete" is in the file, but not sure if there's a better way.

Parents
  • We have a dedicated dashboard in our Tools View which our team use as part of Daily Checks:

    It uses the below SWQL

    SELECT
    TOLOCAL(ST.EventTime) AS [Start Time]
    ,TOLOCAL(ET.EventTime) AS [Finish Time]
    ,CONCAT(--Downtime formatted  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60) >= 1 --Hours  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60%24, 'h ') ELSE '' END),  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60) >= 1 --Minutes  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60%60, 'm ') ELSE '' END),  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))) >= 1 --Seconds  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))%60, 's ') ELSE '' END)) AS [Duration]
    FROM Orion.Events ST
    LEFT JOIN Orion.Events ET ON ET.NetObjectID = ST.NetObjectID 
        AND ET.EventTime = (SELECT TOP 1 x.EventTime FROM Orion.Events x WHERE
                            x.EventTime >= ST.EventTime AND x.EventType = 1000
                            AND x.Message = 'Nightly Maintenance Completed'
                            AND x.NetObjectID = ST.NetObjectID
                            ORDER BY x.EventTime)
    WHERE
        ST.EventType = '1000'
        AND ST.Message = 'Starting Nightly Maintenance'
        AND ST.EventTime > ADDDAY(-7,GETUTCDATE())
    ORDER BY ST.EventTime DESC

    I do like the idea of an alert if the time exceeds a point, but I'll see :P

Reply
  • We have a dedicated dashboard in our Tools View which our team use as part of Daily Checks:

    It uses the below SWQL

    SELECT
    TOLOCAL(ST.EventTime) AS [Start Time]
    ,TOLOCAL(ET.EventTime) AS [Finish Time]
    ,CONCAT(--Downtime formatted  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60) >= 1 --Hours  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60/60%24, 'h ') ELSE '' END),  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60) >= 1 --Minutes  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))/60%60, 'm ') ELSE '' END),  
            (CASE WHEN (SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))) >= 1 --Seconds  
                THEN CONCAT(SECONDDIFF(ST.EventTime,ISNULL(ET.EventTime, GETUTCDATE()))%60, 's ') ELSE '' END)) AS [Duration]
    FROM Orion.Events ST
    LEFT JOIN Orion.Events ET ON ET.NetObjectID = ST.NetObjectID 
        AND ET.EventTime = (SELECT TOP 1 x.EventTime FROM Orion.Events x WHERE
                            x.EventTime >= ST.EventTime AND x.EventType = 1000
                            AND x.Message = 'Nightly Maintenance Completed'
                            AND x.NetObjectID = ST.NetObjectID
                            ORDER BY x.EventTime)
    WHERE
        ST.EventType = '1000'
        AND ST.Message = 'Starting Nightly Maintenance'
        AND ST.EventTime > ADDDAY(-7,GETUTCDATE())
    ORDER BY ST.EventTime DESC

    I do like the idea of an alert if the time exceeds a point, but I'll see :P

Children
No Data