Delete DEMO alerts from SQLAgent
Since the days of SQL 70 automated administrative tasks are getting better with the usage of SQLAgent, you need to know how to configure alerts and set up operators, the people SQL Server contacts when alert conditions arise. SQLAgent Jobs can trigger alerts, for example, when a scheduled job fails or when a log file fills up. You can also define alerts to trigger jobs in response to an alert condition.
By default SQL Server will get installed with Demo alerts and you can leave them as it is or drop them. You can simply use the demo alerts to tell you if something serious has happened. To view them in Enterprise Manager:-
Open SQL Server Agent
Choose Operators
Right Click and choose New Operator
Give it a name
To delete the demo alerts:
-- For log file full
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'ALERT_DB_TEMPDB log file'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'N'ALERT_DB_TEMPDB log file'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'N'ALERT_DB_TEMPDB log file',
@message_id = 9002, @severity = 0, @enabled = 1, @delay_between_responses = 10,
@include_event_description_in = 5, @database_name = N'tempdb', @category_name = N'[Uncategorized]'
END
-- For data file
IF (EXISTS (SELECT name FROM msdb.dbo.sysalerts WHERE name = N'ALERT_DB_TEMPDB data file'))
---- Delete the alert with the same name.
EXECUTE msdb.dbo.sp_delete_alert @name = N'ALERT_DB_TEMPDB data file'
BEGIN
EXECUTE msdb.dbo.sp_add_alert @name = N'ALERT_DB_TEMPDB data file',
@message_id = 1105, @severity = 0, @enabled = 1, @delay_between_responses = 10,
@include_event_description_in = 5, @database_name = N'tempdb', @category_name = N'[Uncategorized]'
END