Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
The Trade-off Between SQL Server Security and Performance - Free Webinar
 

SQL Server Database Mail Health Check with Policy Based Management


By:   |   Last Updated: 2017-12-27   |   Comments   |   Related Tips: More > 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:

Conditions - Description: New Conditions

Demo Condition - DB Mail Check - Server Configuration

"_Demo Condition: DB Mail Check (Server Configuration)" Condition - Description: "_Demo Condition: DB Mail Check (Server Configuration)" Condition

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...":
Create Condition - Description: Create 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:
Condition Advanced Edit - Description: Condition 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:
SQL Server Agent Enable Profile option - Description: SQL Server Agent Alert System Properties - Enable Profile option
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:
SQL Server Agent Mail  Profile option - Description: SQL Server Agent Alert System Properties - Mail  Profile option
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:
"_Demo Condition: DB Mail Check (Server Setup)" Condition - Description: "_Demo Condition: DB Mail Check (Server Setup)" Condition

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
"Enterprise or Standard Edition"  Condition - Description: "Enterprise or Standard Edition"  Condition

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', 
   @[email protected]_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:

New Policies - Description: New Policies

Demo Policy - DB Mail Check - Server Setup

In SSMS go to Management > Policy Management > Policies:

  • Right click "Policies", then click "New Policy...":
Create New Policy - Description: Create 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:
"_Demo Policy: DB Mail Check (Server Setup)" Policy - Description: "_Demo Policy: DB Mail Check (Server Setup)" Policy
  • 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):
"_Demo Policy: DB Mail Check (Server Setup)" Policy Description - Description: "_Demo Policy: DB Mail Check (Server Setup)" Policy Description
  • 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:
"_Demo Policy: DB Mail Check (Server Configuration)" Policy - Description: "_Demo Policy: DB Mail Check (Server Configuration)" Policy
  • 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):
"_Demo Policy: DB Mail Check (Server Configuration)" Policy Description - Description: "_Demo Policy: DB Mail Check (Server Configuration)" Policy Description
  • 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:
Registered Servers - Description: Registered Servers in SSMS
  • Click "Evaluate Policies..."
  • Select "Server" as a Source and enter a CMS name where Policies and Conditions were created:
Evaluate Policies - Source - Description: Evaluate Policies - Source - Server
  • Click "OK". Select two Policies we have created for the Database Mail Checks:
Evaluate Policies - Description: Evaluate Policies
  • As we are using T-SQL Scripts in Conditions Expressions you will get this warning:
Warning - Description: Evaluate Policies - Security 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):

Evaluate Policies - Results - Description: Evaluate Policies - Results

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:

Evaluate Policies - Results Details - Description: Evaluate Policies - Results Details

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.


Last Updated: 2017-12-27


next webcast button


next tip button



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

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Learn more about SQL Server tools