Resetting log growth alert
-
Hello!
I have defined performance condition alert to get notified when log file in database is growing. I setup alert in the following way:
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'Pubs log growth'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'Pubs log growth'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'Pubs log growth', @message_id = 0, @severity = 0, @enabled = 0, @delay_between_responses = 0, @notification_message = N'Pubs log has grown on IMARCHENKO server', @performance_condition = N'SQLServer
atabases|Log Growths|pubs|>|1', @include_event_description_in = 0, @job_name = N'Send log growth alert', @category_name = N'[Uncategorized]'END
N'Send log growth alert' is simply sending out an email.
I am getting notified every minutes since event occurs. I was trying to reset number of occurances: exec msdb..sp_update_alert @name = N'Pubs log growth'@occurrence_count=0 but I am still getting an email every minute.
I am not sure how I can reset this alert. Another issue is that when I say Alert if counter becomes equal to 1 (meaning alert me only once when log expands), I do not get notified at all. I am running long transaction to fill log in and trigger log file growth. In fact, I can see the size growing.
Any help is greately appreciated,
Igor
-
Igor,
You may want to try increasing the Delay between responses setting on the Response tab of the alert. It is set to 1 minute by default.
HTH.......Michelle
-
I have figured out how to handle this issue. I am retrieving current log growths value from sysperfinfo and use it as initial Value in alert. Once alert fires, I execute the job that reads new value from sysperfinfo and updates alert with new Value. It works fine so far.
Igor
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply