Script to clear alerts

johnmcp

Does anyone have a SQL script that I can run against the SQL Monitor database to clear alerts?

I have over 600k uncleared alerts for a particular server and it is bogging down the web application and I can't clear them.

Many thanks...

johnmcp

If anyone is interested I used the script below:

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 '%<[i]server/cluster name[/i]>%'
AND AType.Name = '<[i]alert type[/i]>'

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)