SQL Server Database Mail Health Check with Policy Based Management

By: Svetlana Golovko   |   Updated: 2017-12-27   |   Comments   |   Related: > Database Mail

Problem

In our latest tip we have provided scripts to check your servers for issues related to SQL Server Database Mail.  Email alerting is an important feature that helps to automate a lot of Database Administration tasks. How can we be sure that Database Mail works properly? If there is email problem there is no another email that will notify us of an issue and it could be days before we realize that there is a problem.

Solution

In this tip we will provide a solution to check all of your servers for the different types of Database Mail issues. We are going to use Policy-Based Management (PBM) to perform these checks.

We will create several conditions and policies based on existing Facets or T-SQL queries. As SQL Server Express edition doesn't support the Database Mail our policy checks will run only on Standard, Developer or Enterprise SQL Server Edition.

Prerequisites and Assumptions:

• Target SQL Servers versions are from 2008 R2 to 2017.
• We assume that only one mail account and one mail profile is setup on each monitored SQL Server instance. If you have a different configuration you may need to do extra testing and modify the script.
• CMS is configured and all SQL Servers are registered there.

Policy Based Management Conditions

We will create the following two PBM Conditions:

Demo Condition - DB Mail Check - Server Configuration

This condition has 9 different expressions.

• In SQL Server Management Studio (SSMS) go to Management > Policy Management > Conditions
• Right click "Conditions", then click "New Condition...":

• Enter the name of the condition: "_Demo Condition: DB Mail Check (Server Configuration)"
• Select "Server Configuration" Facet
• Click on the button next to “Field” column under "Expression"
• Add the following expression under “Advanced Edit:

Click “OK”.

The full expressions is:

ExecuteSql('Numeric', 'SELECT  COUNT(*) FROM msdb.dbo.sysmail_account')>0   

This expression will check if a Mail Account exists (msdb.dbo.sysmail_account table).

• The following expressions are:
  • AgentXPsEnabled - Agent XPs Server Configuration Option. indicates whether SQL Server Agent extended stored procedures are available or not:

@AgentXPsEnabled = True

• Database Mail is enabled:

@DatabaseMailEnabled = True

• There are no failed emails during last 24 hours. We are checking msdb.dbo.sysmail_event_log table for errors that occurred during last 24 hours:

ExecuteSql('Numeric', 'SELECT COUNT(*) AS [FailedEmails] 
FROM msdb.dbo.sysmail_event_log where event_type=''error'' 
and log_date > GETDATE() -1 ') = 0

• A Mail Profile is configured on SQL Server (msdb.dbo.sysmail_profile table):

ExecuteSql('Numeric', 'SELECT COUNT(*) FROM msdb.dbo.sysmail_profile')  

• SQL Server Agent is enabled and started. We use the sys.dm_server_services Dynamic Management View for this check:

ExecuteSql('String', 'SELECT status_desc 
FROM sys.dm_server_services WHERE servicename LIKE ''SQL Server Agent%''') = 'Running'
   

• A Mail Profile is enabled under SQL Server Agent properties ("Alert System" tab). This setting is saved under the SQL Server registry, so we use master.dbo.xp_instance_regread extended stored procedure to get the value for this property:

ExecuteSql('Numeric', 'DECLARE @SQLAgentMailEnabled INT = 0
   EXECUTE master.dbo.xp_instance_regread 
        N''HKEY_LOCAL_MACHINE'',
        N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'',
        N''UseDatabaseMail'',
        SQLAgentMailEnabled OUTPUT;
        SELECT @SQLAgentMailEnabled;') = 1

• A Mail Profile is assigned for the SQL Server Agent properties ("Alert System" tab). This setting is also saved in the registry:

ExecuteSql('String', 'DECLARE @SQLAgentMailProfileEnabled VARCHAR(30) 
   EXECUTE master.dbo.xp_instance_regread N''HKEY_LOCAL_MACHINE'',
        N''SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent'',
        N''DatabaseMailProfile'',
        @SQLAgentMailProfileEnabled OUTPUT;
        SELECT @SQLAgentMailProfileEnabled;') != ''

• Click "OK" to cave the Condition once you completed entering the expressions above.

Demo Condition - DB Mail Check - Server Setup

This Condition will check if SQL Server Agent Service is set to start-up automatically.

• Right click "Conditions", then click "New Condition..."
• Enter the name for the Condition: "_Demo Condition: DB Mail Check (Server Setup)"
• Select "Server Installation Settings" Facet
• Select "=" Operator
• Select the "@AgentStartMode" value from the drop-down list under the Field column
• Select the "Auto" value from the drop-down list under the Value column:

The full expression is:

@AgentStartMode = Auto   

• Click "OK to save the Condition.

Condition to Restrict SQL Servers Editions Checks

Database Mail is not supported in the SQL Server Express Edition, so we will be evaluating our policies only against SQL Server Developer, Standard or Enterprise Edition.

In order to set the restriction we need to create the new Condition.

Create the condition that will be used to check the data file extensions:

• Right click "Conditions", then click "New Condition..."
• Enter the name of the condition: "Enterprise or Standard Edition"
• Select "Server" facet
• Select the "@EngineEdition" value from the drop-down list under the Field column
• Select "=" Operator
• Select the "Standard" value from the drop-down list under the Value column
• Select the "@EngineEdition" value from the drop-down list under the Field column for the second line
• Select "=" Operator
• Select the "EnterpriseOrDeveloper" value from the drop-down list under the Value column

The full expression is:

@EngineEdition = Standard 
OR 
@EngineEdition = EnterpriseOrDeveloper  

• Click "OK to save the Condition.

Create the New Policy Category

We will create the new Policy Category for our Database Mail Checks:

DECLARE @policy_category_id INT
EXEC msdb.dbo.sp_syspolicy_add_policy_category 
   @name=N'DB Mail Health Check', 
   @policy_category_id=@policy_category_id OUTPUT
 
SELECT @policy_category_id   

Using a separate Category simplifies Polices Checks automation with PowerShell Scripts. It allows running multiple Policy Checks by using the category name.

Here is an example of a simple PowerShell Script:

sl "SQLSERVER:\SQLPolicy\CMSSQLSRV\default\Policies"
gci | Where-Object {$_.PolicyCategory -eq "DB Mail Health Check"} |
Invoke-PolicyEvaluation -TargetServer SQLSRVTEST1

PBM Policies to Check Database Mail issues

For each Condition we created above we will create a new Policy:

Demo Policy - DB Mail Check - Server Setup

In SSMS go to Management > Policy Management > Policies:

• Right click "Policies", then click "New Policy...":

• Enter the name of the policy: "_Demo Policy: DB Mail Check (Server Setup)"
• Select condition "_Demo Condition: DB Mail Check (Server Setup)" under "Check Condition" as shown below
• Select the “Enterprise or Standard Edition” Condition under the “Server restriction” field:

• Click on “Description” page to fill out the rest of the Policy properties
• Select “DB Mail Health Check” Category from the Category Drop-down list
• Enter Description of the Policy
• Enter “Text to display” and “Address” (if applicable):

• Click "OK" to save the Policy.

Demo Policy - DB Mail Check - Server Configuration

• Right click "Policies", then click "New Policy...":
• Enter the name of the policy: "_Demo Policy: DB Mail Check (Server Configuration)"
• Select condition "_Demo Condition: DB Mail Check (Server Configuration)" under "Check Condition" as shown below
• Select the “Enterprise or Standard Edition” Condition under the “Server restriction” field:

• Click on “Description” page to fill out the rest of the Policy properties
• Select “DB Mail Health Check” Category from the Category Drop-down list
• Enter Description of the Policy
• Enter “Text to display” and “Address” (if applicable):

• Click "OK" to save the Policy.

Evaluate the Policies

There are several ways to evaluate the policies:

• You can use Registered Servers in SQL Server Management Studio (SSMS) to evaluate the policy. Refer to this tip for more information.
• Policies can be evaluated using Central Management Server (CMS). Refer to this tip for the details.
• Another option is to schedule policy evaluation by setting the Execution Mode to "On Schedule". Refer to this tip for an example.
• There is also an option to evaluate policies using PowerShell and schedule it as a SQL Server Agent Job.

Evaluating the Policies using a CMS Example

To evaluate the Policies manually using the Central Management Server (CMS) use Registered Servers:

• In SSMS, on the View menu, click "Registered Servers"
• Right-click on a CMS name or on a Registered Servers Group:

• Click "Evaluate Policies..."
• Select "Server" as a Source and enter a CMS name where Policies and Conditions were created:

• Click "OK". Select two Policies we have created for the Database Mail Checks:

• As we are using T-SQL Scripts in Conditions Expressions you will get this warning:

• We know that these Scripts are safe, so we can click "Run" to start our Policies Evaluation.

Note on the image below that the Policies Evaluation didn't run against SQL Server Express Edition (DEMOSRV5_EXPRESS SQL Server):

In our Example the "_Demo Policy: DB Mail Check (Server Configuration)" Policy has failed for the "DEMOSRV4" SQL Server. We can see the details of the failed policy when we click on the "View..." link:

There were 3 failed emails during last 24 hours, so the Policy Check failed and we need to investigate what was the problem, why did the emails fail?

Additional Notes

Keep in mind that often when you run monitoring (Policies Checks) from a specific SQL Server (for example CMS) you can miss this server's issues itself, so make sure that Database Mail works on this SQL Server.

This tip has two XML files with exported Policies attached. You can recreate the Conditions and the Policies above by importing these Policies. Read this tip about Importing SQL Server Policies with Policy-Based Management.

Next Steps

• Check your environment for the failed emails using the Policies provided.
• Read all Database Mail tips here.
• Find out more about Database Mail Configuration here.
• Here is a reference to the Database Mail System Views.
• This tip has troubleshooting steps for some of the Database Mail issues.
• Read more about Database Mail here.
• Alternative way of sending emails from this edition of SQL Server could be found here.
• Download the Policy-Based Management XML files here.

About the author

Svetlana Golovko is a DBA with 13 years of the IT experience (including SQL Server and Oracle) with main focus on performance.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips