Reset count for all alerts

,

Restart count from zero for all alerts using the propre sp

-- SSC script
-- Restart count from zero for all alerts using the propre sp
-- johan bijnens - ALZDBA - dd 14/01/2003
-- tested with sql2000 / SQL2005 / SQL2008

declare @id int
declare @name sysname
declare @occurrence_count int, @count_reset_date int, @count_reset_time int

DECLARE @curr_date INT, @curr_time INT 
-- got this piece using profiler !!
SELECT @curr_date = CONVERT(INT, CONVERT(CHAR, GETDATE(), 112)), @curr_time = (DATEPART(hh, GETDATE()) * 10000) + (DATEPART(mi, GETDATE()) * 100) + (DATEPART(ss, GETDATE()))  
 
DECLARE C1 cursor for 
select id, name, occurrence_count, count_reset_date, count_reset_time 
from msdb..sysalerts 
-- where id > 9  -- default demo alerts SQL2000
where occurrence_count > 0
order by name
for read only

OPEN C1
FETCH NEXT FROM c1 
INTO @id, @name, @occurrence_count, @count_reset_date, @count_reset_time

WHILE @@FETCH_STATUS = 0
BEGIN
        EXECUTE msdb.dbo.sp_update_alert @name = @name, @count_reset_date = @curr_date, @count_reset_time = @curr_time, @occurrence_count = 0

print 'Counter as been reser for Alert : [' + @name + '] former count ' + ltrim(convert(varchar(25),@occurrence_count)) + ' (former reset date/time : ' + ltrim(convert(varchar(25),@count_reset_date)) + ' / ' +  ltrim(convert(varchar(25),@count_reset_time)) + ' ) '

FETCH NEXT FROM c1 
INTO @id, @name, @occurrence_count, @count_reset_date, @count_reset_time

end
CLOSE C1
DEALLOCATE c1

Rate

Share

Share

Rate