Script to clear alerts

SQL Server performance monitoring and alerting

Moderators: eddie davis, priyasinha, Adam, chriskelly, Chris Lambrou, Chris Spencer

Script to clear alerts

Postby johnmcp » Thu Nov 28, 2013 2:21 pm

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
 
Posts: 9
Joined: Wed Apr 17, 2013 8:46 am

Postby johnmcp » Fri Nov 29, 2013 1:26 pm

If anyone is interested I used the script below:

Code: Select all
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)
johnmcp
 
Posts: 9
Joined: Wed Apr 17, 2013 8:46 am


Return to SQL Monitor 3

Who is online

Users browsing this forum: No registered users and 0 guests