April 16, 2015 by Mike Hillwig
In my environment, we enable database mail on every instance. While we have very few clients or applications that use it, we use it to have our servers call for help when something goes wrong. That means it’s really important that database mail be configured properly. One of the things we have on our peer review checklist is to test database mail. One day, while working with a member of my team, I saw how cumbersome it was to click through the GUI to send a test email. This little script was quickly born:
declare @body nvarchar(1000) declare @subject nvarchar(1000) select @body = 'This is a mail test from '+@@servername select @subject = 'Mail test from '+@@servername EXEC msdb.dbo.sp_send_dbmail @profile_name = 'dbmail', @firstname.lastname@example.org', @body=@body, @subject=@subject;
If you see something that shows your message was queued, it’s a good indication that SQL Server was able to send the mail message.
The next step is to validate that the message was received. I can’t tell you how many times I’ve sent messages that just never made it through.