clearing all alerts SQL Monitor 5.0.1.1302

karl.oliverkarl.oliver Posts: 33 Bronze 1
edited February 11, 2016 6:49AM in SQL Monitor Previous Versions
hello
how do i clear all alerts- over 1000, the 2 post i have found on the forum so far dont work one of them being the sql below
using SQL Monitor 5.0.1.1302

this sql did not clear them

DECLARE @Alerts TABLE (AlertId INT, Date BIGINT)

INSERT INTO @Alerts
( AlertId, Date )
SELECT A.AlertId, ASev.Date
FROM alert.Alert A INNER JOIN alert.Alert_Severity ASev ON A.AlertId = ASev.AlertId
INNER JOIN alert.Alert_Type AType ON A.AlertType = AType.AlertType
WHERE A.TargetObject LIKE '%<server/cluster name>%'
AND AType.Name = '<alert type>'

INSERT INTO alert.Alert_Cleared
( AlertId, Date, Cleared )
SELECT A.AlertId, A.Date, 1
FROM @Alerts A

UPDATE alert.Alert SET [Read]=1
WHERE AlertId IN
(SELECT AlertId from @Alerts)

Comments

  • Hello Karl,
    Thanks for your inquiry about SQL Monitor.

    To clear the alerts:,
    1. Go to the Alerts page on the SQL Monitor GUI,
    2. Select the "SELECT" drop down button and click the option ALL (Be sure to click the "Select all alerts across all pages" if you want to delete alerts from more than one page)
    3. To clear the alerts select the CLEARED button to the right of the SELECT button.

    Sean Quigley | Product Support Engineer | Redgate Software

    Have you visited our Help Center?





  • janklajankla Posts: 4 New member
    Hi,

    This qeury is used by me for the past 6 months. Seems to do the trick:

    DECLARE @now BIGINT
    SET @now = utils.DateTimeToTicks(GETUTCDATE())

    ;WITH cte AS (
    SELECT acu.[AlertId]
    FROM [alert].[Alert_Current] acu
    LEFT JOIN [alert].[Alert_Cleared] ac
    ON ac.AlertId = acu.AlertId
    WHERE (ac.AlertId IS NULL) AND (acu.[Event]=1 OR acu.[LastSeverity] = 0)
    )

    INSERT INTO alert.[Alert_Cleared]
    (
    [AlertId],
    [Date],
    [Cleared]
    )
    SELECT
    [AlertId],
    @now,
    1
    FROM
    cte

    GO
Sign In or Register to comment.