Having had to run through this process again recently, I thought I’d tidy up my notes on all of this, and put together a proper how-to post 🙂

When SQL Server 2005 was released, the added functionality of Database Mail came with it, giving us the ability to use SMTP to send email from SQL Server instances.  This was a huge leap forward from SQL Server 2000 SQL Mail, in that you no longer needed to set-up a mail client first (such as Outlook) to send emails.

If for some reason you’re still using SQL Server 2000 somewhere (I know people that do!), you would have to setup a MAPI client first, in which case this article is worth a read.

NOTE: Database Mail is not supported in Express Editions of SQL Server, although I have heard of reports that 2008/2012 Express Editions can still be configured using the method I’ve outlined in this post (haven’t tested this myself though).

First of all, let’s start by saying there are two methods of enabling Database Mail…

One is to use the GUI, by navigating to the area below within SQL Server Management Studio, and following the on-screen prompts as part of the configuration wizard…

However, this can become quite time consuming…especially when you’re setting up Database Mail for multiple instances!

The second method is to use Transact SQL (T-SQL) and stored procedures to enable and configure Database Mail, which is much quicker, and less prone to human error, after the initial run has been tested and confirmed as working.

This walk through will execute a number of stored procedures to accomplish the following tasks:

• Enable the Database Mail feature
• Create a profile for Database Mail
• Create an account for use with the profile
• Send a test email to the DBAs email address
• Enable the SQL Server Agent to use Database Mail
• Add the ‘DBAs’ as an operator for notifications

Before running any of scripts below, I would take a backup of your system databases (master, msdb), and ensure you have sysadmin rights.

1) Enable the Database Mail XPs:

USE master
GO
sp_configure 'show advanced options',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_configure 'Database Mail XPs',1
GO
RECONFIGURE 
GO

2) Create a new mail profile:

USE msdb
GO
EXECUTE msdb.dbo.sysmail_add_profile_sp
@profile_name = 'admin',
@description = 'Profile for sending Automated DBA Notifications'
GO

3) Create an account for the notifications (changing the email address, mail server, port as appropriate to your environment):

EXECUTE msdb.dbo.sysmail_add_account_sp
@account_name = 'SQLAlerts',
@description = 'Account for Automated DBA Notifications',
@email_address = 'sqlalerts@example.com',
@display_name = 'SQL Alerts',
@mailserver_name = 'smtp.example.com',
@port = 25
GO

4) Add the account to the profile:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
@profile_name = 'admin',
@account_name = 'SQLAlerts',
@sequence_number = 1
GO

5) Enable the SQL Server Agent to use Database Mail profile by updating the registry settings:

USE msdb
GO
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'UseDatabaseMail',
N'REG_DWORD', 1
EXEC master.dbo.xp_instance_regwrite
N'HKEY_LOCAL_MACHINE',
N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
N'DatabaseMailProfile',
N'REG_SZ',
N'admin'

NOTE: (0 row(s) affected) is normal here 😉

6) Restart the SQL Server Agent:

At this point, the SQL Server Agent needs to be restarted. If it isn’t, the Database Mail configuration changes will not be picked up, and the Database Mail process will not start / function correctly.

If Database Mail is being configured on a SQL Server cluster, you’ll need to perform this using the Cluster Administrator tool by selecting the appropriate cluster group, then restarting the SQL Server Agent resource for the appropriate instance:

Windows Server 2003:
C:WINDOWSClusterCluAdmin.exe

Windows Server 2008:
C:WindowsSystem32Cluadmin.msc

7) Once the SQL Server Agent has been restarted, try sending an email to test the configuration is working as expected:

EXECUTE msdb.dbo.sp_send_dbmail
@profile_name = 'admin',
@recipients = 'mail@example.com',
@Subject = 'Test Message generated from SQL Server Database Mail',
@Body = 'This is a test message from SQL Server Database Mail'
GO

8) Finally, setup an operator called ‘DBAs’ for the job notifications (24×7 schedule in this case) for the email address you supplied earlier:

EXEC msdb.dbo.sp_add_operator @name=N'DBAs', 
@enabled=1, 
@weekday_pager_start_time=0, 
@weekday_pager_end_time=235959, 
@saturday_pager_start_time=0, 
@saturday_pager_end_time=235959, 
@sunday_pager_start_time=0, 
@sunday_pager_end_time=235959,
@pager_days=127, 
@email_address=N'sqlalerts@example.com', 
@category_name=N'[Uncategorized]'
GO

That’s it, Database Mail is enabled and ready to use…once you’re happy everything is working as expected, the scripts above can be executed in one go against other instances, then it just needs a restart of the SQL Server Agent at the end 🙂

Set-up job failure notifications

To generate notifications when a job succeeds, fails, or completes, you can run a stored procedures like below on a job by job basis:

USE msdb
GO
EXEC msdb.dbo.sp_update_job @job_name='System databases - backups.Subplan_1',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N'DBAs'
GO

NOTE: The notify_levels can be set to: 1 (job succeeds), 2 (job fails), or 3 (job completes)

…or to enable failure notifications for all jobs, run the following script, which will update the notifications jobs for you, and output the T-SQL that’s been executed for each job identified:

DECLARE @JobName SYSNAME, @JobID UNIQUEIDENTIFIER, @NotifyLevel INT, @SQL NVARCHAR(3000)

DECLARE job_operator_cursor CURSOR FOR
SELECT name, job_id, notify_level_email FROM msdb.dbo.sysjobs_view 

OPEN job_operator_cursor
FETCH NEXT FROM job_operator_cursor INTO @JobName, @JobID, @NotifyLevel
WHILE @@FETCH_STATUS = 0
BEGIN
IF NOT EXISTS(SELECT 1 FROM msdb.dbo.sysjobs_view WHERE notify_level_email = 2 and name LIKE @JobName)
BEGIN
PRINT ''
SELECT @SQL = 'EXEC msdb.dbo.sp_update_job @job_name=N'''+@JobName+''',
@notify_level_email=2,
@notify_level_netsend=2,
@notify_level_page=2,
@notify_email_operator_name=N''DBAs'''
PRINT @SQL
EXEC sp_executesql @SQL
END
FETCH NEXT FROM job_operator_cursor INTO @JobName, @JobID, @NotifyLevel
END

CLOSE job_operator_cursor
DEALLOCATE job_operator_cursor

…or of course, you can still update each of the jobs via the GUI:

SQL Server > Jobs > Right-click (on the apprriate job) > Properties > Notifications tab

Then you simply tick the “E-mail” option, select an operator (DBAs) and choose to alert the operator when the job succeeds, fails, or completes:

Quick troubleshooting queries for Database Mail

Check to see if the service broker is enabled (should be 1):

SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'

Check to see if Database Mail is started in the msdb database:

EXECUTE dbo.sysmail_help_status_sp

…and start Database Mail if necessary:

EXECUTE dbo.sysmail_start_sp

Check the status of the mail queue:

sysmail_help_queue_sp @queue_type = 'Mail'

Check the Database Mail event logs:

SELECT * FROM sysmail_event_log

Check the mail queue for the status of all items (including sent mails):

SELECT * FROM sysmail_allitems

There is a good set of articles on MSDN for troubleshooting Database Mail problems here.