Red Gate forums :: View topic - Script to clear alerts
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
SQL Monitor 3
SQL Monitor 3 forum

Script to clear alerts

Search in SQL Monitor 3 forum
Post new topic   Reply to topic
Jump to:  
Author Message
johnmcp



Joined: 17 Apr 2013
Posts: 9

PostPosted: Thu Nov 28, 2013 2:21 pm    Post subject: Script to clear alerts Reply with quote

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...
Back to top
View user's profile Send private message
johnmcp



Joined: 17 Apr 2013
Posts: 9

PostPosted: Fri Nov 29, 2013 1:26 pm    Post subject: Reply with quote

If anyone is interested I used the script below:

Code:
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)
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group