Alert in sql server agent

With the help of alert we can automatically notify database administrators or groups when unexpected error occurs. We can notify by below media

Ø  E-Mails

Ø  Pager

Ø  Net  send

Let’s start step by step to create Alert in sql server agent

Open the sql server and start sql server agent.

Right click on the Alert and select new Alert.

You will get New Alert window. Write the Alert name and select Enable check box.

Select Alert Type. There is bellowing type of Alert.

Ø  Sql server event alert: -

If you are creating alert for like fatal error or sql syntax errors or hardware related issue or etc the select this type of alert.

Ø  Sql server performance condition alert :-

If you are creating alert for sql server performance related alert like CPU load or free disk space etc then use this type of alert.

Ø  WMI event alert  :-

If you are creating alert for Windows Management instruments (system related information) the go with WMI events.

I am selected sql server event Alert.

Now we can select database name on which we want create Alert. We can also create it on all databases.

Select Error number or severity.

You can check the message text and write your message.

Now go the response tab.

If you want to assign the Alert to the particular job the check the Execute job and select the job name.

Don’t worry if you are able to create job. We will learn how to create jobs in sql server agent in next post.

In same way if you want to notify to the operator you can select that operator. In next post we will learn how to create operator.

Select Options

In this section you select the medium to send the alert notification. You can also write the additional notification message in the text box.

Now click ok. Alert is created.

If you want to generate the script of this alert the click on the script.

You will get the below script.

USE [msdb] GO EXEC msdb.dbo.sp_add_alert @name=N'My Database Alert',               @message_id=0,               @severity=1,               @enabled=1,               @delay_between_responses=0,               @include_event_description_in=1,               @notification_message=N'my first job alert.',               @event_description_keyword=N'Sql job failed',               @job_id=N'00000000-0000-0000-0000-000000000000' GO

You can create the Alert by two ways. GUI or using script.  Alert has been created.

Now it is ready to use.

Hope this will be help you.