Configuration: Sending Emails with Database Mail
Database mail helps administrators know when something went terribly awry on a SQL Server, like corruption, a failed restore, or the system’s about to go offline due to a serious issue. It doesn’t catch everything, but if you configure it right, it’ll give you a quick heads-up about most common issues. Let’s cover how to set it up.
In the video, I cover these queries:
Transact-SQL
|
1 2 3 4 5 6 7 |
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb; SELECT * FROM sys.configurations WHERE name = 'Database Mail XPs'; |
And:
Transact-SQL
|
1 2 3 4 5 6 |
EXEC sp_configure 'show advanced option', '1'; RECONFIGURE GO EXEC sp_configure 'Database Mail XPs', 1; RECONFIGURE GO |
- Backups 1: 3 Common Strategies
- Backups 2: Restores
- Backups 3: Setting Up Maintenance Plans
- Backups 4: Setting Up Ola Hallengren’s Maintenance Scripts
- Backups: Reading from Databases During Restores
- Configuration: Anti-Virus
- Configuration: Instant File Initialization
- Configuration: Lock Pages in Memory (LPIM)
- Configuration: Prepare for Emergencies with the Remote DAC
- Configuration: sp_configure Settings
- Configuration: TempDB Files and Sizes
- Corruption 1: How it Happens, and How to Detect It
- Corruption 2: DBCC CHECKDB for VLDBs
- Maintenance: Agent Jobs
- Maintenance: Patches: Which Ones to Apply, When, and How
- Maintenance: Shrinking Files
4 Comments. Leave new
Thanks for the course on the Fundamentals of DB Admin….I’m learning a lot.
Just as an FYI, I noticed a missing quote in the first query in the Sending Email with DB Mail section…
SELECT is_broker_enabled
FROM sys.databases
WHERE name = ‘msdb;
For those that want a ready-made TSQL script to configure Database Mail, I posted a script I wrote forever ago that I’ve used for each of the SQL Servers I managed at the job I had then and all the jobs I’ve had since then.
All the cool kids are using PowerShell now, and it’s what Brent recommends, but this is something you can use for now to avoid dealing with all those dialog boxes: https://m60freeman.blogspot.com/2011/11/configure-database-mail.html.
If anyone writes a PowerShell equivalent (or better), please post a comment here or on my blog post as I’d love to see it.
A friend of mine is an accidental DBA, and does not know the password to the account used for the DB email. Now, this password can be reset, but the other option is to create a new email account. Which one of these options should my friend choose?
For support questions, your best bet is a Q&A site like https://dba.stackexchange.com.