Configure Database Mail in SQL server 2008

Database Mail

First setup database mail with a profile named SQLAlerts. The profile can be named anything but in these instructions, the profile name SQLAlerts is referenced. If you wish to use a different profile name just substitute accordingly. For procedures in setting up database mail

Define Operator

Connect to the instance using Microsoft SQL Management Studio

Double Click SQL Server Agent

Right Click on Operators and select New Operator



Specify an operator Name, E-mail name, and click OK


Setup SQL Agent Settings

Right Click SQL Server Agent > select Properties


Select Alert System in the left pane

  • Checkmark > Enable mail profile
  • Verify Mail system: Database Mail
  • Verify Mail Profile: SQLAlerts
  • Checkmark > Include body of e-mail in the notification message
  • Click OK.


Restart SQL Agent to activate settings.

Warning: Restarting SQL Agent will cancel any executing jobs.


Define Alert

This sample alert will send an email when TEMPDB database gets larger than 0 KB. This setting is set zero so the alert can be tested. Once verified, you will need to update its settings to a reasonable amount or disable it.

Right Click Alerts and select New Alert


On the General pane specify

  • Name: TEMPDB Growing
  • Type: SQL Server performance condition alert
  • Object: SQLServer:Databases
  • Counter: Data File(s) (KB)
  • Instance: tempdb
  • Alert if counter: rises above
  • Value: 0


Click Response in left pane

  • Checkmark > Notify operators
  • Checkmark > E-mail for the operator


Click Options in left pane

  • Checkmark > Include alert error text in E-mail
  • Delay between response: 2 minutes

Click OK


Verify Alert is Working

The operator should receive an e-mail if not see the troubleshooting section.

Open the Alert. Click History in the left pane.

The fields are updated when the alert is triggered.


 

 

Thanks

 

Prashant Deshpande

Advertisements

Leave a comment

Filed under Uncategorized

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s