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 = 'firstname.lastname@example.org', @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:
Windows Server 2008:
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 = 'email@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=Nfirstname.lastname@example.org', @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:
…and start Database Mail if necessary:
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.
Thank you so much. Amazing help.
Thanks Kayleigh…glad it helped!
All these settings work wonderful .
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 ?
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…
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?
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…
This was a great help and considering how time consuming GUI is this saves me time as well. Thank you so much for sharing.
Wonderful article mate
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 for the feedback Rick, especially regarding the registry entry part…
Very helpful article!
Even in Sql Server Express version you can configure Database Mail using the Stored Procedures.
Thank you for your post. Very very helpful. Setup of DB Mail done in 5 minutes 🙂
Thanks for the post . DB mail configuration done in 5 minutes .
Great post, had DB Mail setup on SQL Express and worked first time!
Great piece!!! Saved me a bunch of time looking for all this information.
Thanks for the post, works great, You’re a life saver 😀
Yes, indeed!!! I was looking for this! Simple and Objective!!
Thanks for the post. Saved a lot of time 🙂
Really great and saved my day thanks!!
Thank you very much indeed, easy and intuitive tutorial!
Thanks a lot. Very usefull 🙂
Thanks a lot. It is useful for me.
Excellent article! Very clear and easy to follow. Thank you so much for taking the time to share with everyone.