Enabling and configuring Database Mail in SQL Server using T-SQL

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.

23 thoughts on “Enabling and configuring Database Mail in SQL Server using T-SQL

  1. All these settings work wonderful .
    But

    I need to send my error message about why the task has failed in the body of the Mail .
    How do i send the error msg in the body ?

    Or

    Can i call a stored procedure to send a customized mail with error as body Whenever a Certain JOB fails to execute properly ??

    I mean how do i call a stored procedure when a Job fails ?
    ( Example / test senddb_mail is to be called with my error message from job history as body .)

    • You can’t do this by default using Database Mail, unfortunately, but this article might help. The only thing I would say about this solution, is that I would look at creating a single job that handles all failures and error reporting, rather than potentially changing multiple jobs. Hope this helps…

  2. Good day kind sir and thanks for posting this! I’m not sure about this but I don’t seem to have a “Database Mail” under the Management nodes in the Object Explorer. I’m pretty sure I’m not using an Express version of MS SQL Server 2008 so I’m quite baffled as to why it’s not there. Would you be so kind to let me know if you have any idea on this, please?

    Thanks!

    • Only SQL Server 2005 and above, excluding Express editions, will list the Database Mail functionality. If you run “select @@version” this will confirm which version you’re running which might help? Other than that, I’m a little baffled I’m afraid…

  3. This was a great help and considering how time consuming GUI is this saves me time as well. Thank you so much for sharing.

  4. Very helpful, thanks.

    Only thing I did differently was enable the Database Mail using the Facets (right click on the server -> Facets -> Surface Area Configuration) because I was testing it on my notebook which is not a server, so the Registry entry didn’t work, but everything else did it.

    Thanks!

  5. Very helpful article!
    Even in Sql Server Express version you can configure Database Mail using the Stored Procedures.

    Thanks!

Leave a comment

Your email address will not be published. Required fields are marked *