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

 

SQL Server Database Mail Health Check for all SQL Servers


By:   |   Read Comments   |   Related Tips: More > Database Mail

SQL Server Indexing Tips and Tricks --- Free MSSQLTips Webcast!


Problem

Most of the SQL Server DBAs will agree that we rely on email alerts too much. Tasks are automated, we have emails notifying us of failed jobs, of errors, etc. But how can we be sure that emails are always sent? If there is an email problem there is not 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 monitor all of your servers for the different types of Database Mail issues. We will provide a T-SQL script that is executed with a PowerShell script. The PowerShell script can be scheduled to run on a Central Management Server (CMS) or another SQL Server. In our example we will run it once a day to check the Database Mail health, but you can modify it and run it every hour or as often as you need.

Prerequisites and Assumptions

Target SQL Servers versions are from 2008 R2 to 2017.

SQL Server Express doesn't support Database Mail, so there will be a failed Database Mail status check for any SQL Server Express. We monitor it in our example, but you can exclude any Express Edition if you want. Alternative ways of sending emails from this edition of SQL Server could be found here.

We assume that only one mail account and one mail profile is setup on each monitored SQL Server Instance. If you have different configurations you may need to do an extra testing and modify the script.

CMS is configured and all SQL Servers are registered, but you can modify the PowerShell script and read the list of SQL Servers from another source.

CMS (or another SQL Server that will be running the Database Mail Check) has PowerShell 4.0 or later (required to append records to a CSV file). This is not required if you are not going to use a PowerShell script and you are going to use only a T-SQL script.

CMS has an SQL Server Agent Proxy Account with permissions to the target SQL Servers (more details later). This is required to schedule the PowerShell script that will be running the T-SQL query on the target SQL Servers.

T-SQL Script to Check for SQL Server Database Mail Issues

With this script we check the following Database Mail related issues:

  • SQL Server Agent is disabled. The Database Mail check will fail right away for SQL Server Express Edition, but it's your choice to include this to the test or filter out all of the SQL Servers Express Editions. We use sys.configurations table to check if SQL Server is enabled.
  • SQL Server Agent is enabled, but not started. We use sys.dm_server_services Dynamic Management View for this check. Keep in mind that this DMV is not available in SQL Server Express.
  • SQL Server Database Mail is disabled. For this particular issue we use sys.configurations Catalog View again.
  • There is no Mail Profile configured on SQL Server (msdb.dbo.sysmail_profile table is empty).
  • A Mail Account doesn't exist (no records in msdb.dbo.sysmail_account table).
  • The Mail Profile is not enabled under SQL Server Agent properties ("Alert System" tab). This setting are saved under SQL Server registry, so we use master.dbo.xp_instance_regread extended stored procedure to get the value for this property:
Enable Mail profile - Description: SQL Server Agent properties (Alert System) - Enable Mail profile
  • Mail Profile is not assigned under SQL Server Agent properties ("Alert System" tab). This setting is also saved in the registry:
Mail profile name - Description: SQL Server Agent properties (Alert System) - Mail profile name
  • There are failed emails during last 24 hours. We are checking msdb.dbo.sysmail_event_log table for errors that occurred during last 24 hours.
  • Sending Test email failed. At the end, we send a test email and wait for the retry interval before we check msdb.dbo.sysmail_event_log table for the errors.

The last 2 checks are the most important as the Database Mail could be configured and all prerequisites met, but there are other issues preventing mail from sending (for example mail server name change/misspelling, firewall rules change, Antivirus Rules preventing emails from being sent etc.).

The Complete Script to Monitor SQL Server Database Mail

SET NOCOUNT ON;

DECLARE 
      @SQLAgentEnabled INT = 0,
      @SQLAgentStarted INT = 0,
      @DBMailEnabled INT = 0, 
      @MailProfileEnabled INT = 0, 
      @MailAccountEnabled INT = 0,
      @SQLAgentMailEnabled INT = 0, 
      @SQLAgentMailProfileEnabled SYSNAME,  
      @failed_emails_last24hours INT, 
      @failed_email_test INT,
      @failed_email_error INT

-- SQL Server Agent enabled
SELECT   @SQLAgentEnabled = CAST(value_in_use AS INT) 
FROM sys.configurations 
WHERE [name] ='Agent XPs';

-- SQL Server Agent status
IF (SELECT CAST(SERVERPROPERTY('Edition') AS VARCHAR(30))) NOT LIKE 'Express Edition%'
BEGIN
   SELECT @SQLAgentStarted = CASE WHEN status_desc = 'Running' THEN 1 ELSE 0 END
   FROM    sys.dm_server_services
   WHERE servicename LIKE 'SQL Server Agent%'
END;

-- SQL Database Mail is enabled
SELECT @DBMailEnabled = CAST(value_in_use AS INT) 
FROM sys.configurations 
WHERE [name] ='Database Mail XPs';

-- @SQLAgentMailEnabled
SELECT @MailProfileEnabled = CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END 
FROM msdb.dbo.sysmail_profile;

-- @MailAccountEnabled
SELECT @MailAccountEnabled  = CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END 
FROM msdb.dbo.sysmail_account;

-- SQL Server Agent is enabled to use Database Mail
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                       N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                       N'UseDatabaseMail',
               @SQLAgentMailEnabled OUTPUT;

-- SQL Server Agent is enabled to use Database Mail and Mail Profile is assigned
EXECUTE master.dbo.xp_instance_regread N'HKEY_LOCAL_MACHINE',
                                       N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent',
                                       N'DatabaseMailProfile',
               @SQLAgentMailProfileEnabled OUTPUT;

-- Testing email
DECLARE @profile SYSNAME, @retry_sec INT, @retry VARCHAR(10)

-- Find Mail profile name
SELECT TOP 1 @profile = [name] 
FROM msdb.dbo.sysmail_profile 
ORDER BY  profile_id ;

-- get email retry interval configuration value
SELECT @retry_sec = paramvalue,
   @retry = RIGHT('0' + CAST(paramvalue / 3600 AS VARCHAR),2) + ':' +
         RIGHT('0' + CAST((paramvalue / 60) % 60 AS VARCHAR),2) + ':' +
         RIGHT('0' + CAST((paramvalue % 60) + 5 AS VARCHAR),2)
FROM msdb.dbo.sysmail_configuration
WHERE paramname = 'AccountRetryDelay';

-- Check if there are failed emails for the last 24 hours
SELECT @failed_emails_last24hours = COUNT(*) 
FROM msdb.dbo.sysmail_event_log
WHERE event_type='error' AND log_date > DATEADD(dd, -1, GETDATE());

-- Send Test email
BEGIN TRY
   EXEC msdb.dbo.sp_send_dbmail
      @profile_name = @profile, 
             @recipients = $(DBA_Email),
             @subject = 'Daily Test DB Mail',
             @body = @@SERVERNAME
END TRY

BEGIN CATCH
   SELECT @failed_email_error = ERROR_NUMBER() 
END CATCH;

-- wait for retry interval (from DB mail configuration) plus 5 more seconds
WAITFOR DELAY @retry
-- WAITFOR DELAY '00:00:05' -- or set it to fixe 5 seconds if you don't want to wait

-- Check if the test email failed
SELECT @failed_email_test = CASE WHEN COUNT(*) > 0 THEN 1 ELSE 0 END
FROM msdb.dbo.sysmail_event_log
WHERE event_type='error' AND
   log_date > DATEADD(ss, @retry_sec + 5, GETDATE());

-- Final report
SELECT @@SERVERNAME AS Server_Name, 
   CAST(GETDATE() AS SMALLDATETIME) AS Run_Date,
   FinalResult = @SQLAgentEnabled * @SQLAgentStarted * @DBMailEnabled * @MailProfileEnabled * @MailAccountEnabled * @SQLAgentMailEnabled * 
         (CASE WHEN @SQLAgentMailProfileEnabled IS NOT NULL THEN 1 ELSE 0 END) * 
         (CASE WHEN ISNULL(@failed_email_error, 0) = 0 THEN 1 ELSE 0 END) *
         (CASE WHEN @failed_emails_last24hours = 0 THEN 1 ELSE 0 END),
   Notes =  CASE WHEN CAST(SERVERPROPERTY('Edition') AS VARCHAR(30)) LIKE 'Express Edition%' 
         THEN 'Express Edition, DB Mail not supported' ELSE 
         CASE WHEN @SQLAgentEnabled = 0 THEN 'SQL Agent disabled; ' 
            ELSE '' END +
         CASE WHEN @DBMailEnabled = 0 THEN 'DB Mail disabled; ' 
            ELSE '' END +
         CASE WHEN @MailProfileEnabled = 0 THEN 'Mail Profile disabled; ' 
            ELSE '' END + 
         CASE WHEN @MailAccountEnabled = 0 THEN 'Mail Account disabled; ' 
            ELSE '' END + 
         CASE WHEN @SQLAgentMailEnabled = 0 THEN 'SQL Agent Mail disabled; ' 
            ELSE '' END + 
         CASE WHEN @SQLAgentMailProfileEnabled IS NOT NULL THEN '' 
            ELSE 'SQL Agent Mail Profile disabled; ' END + 
         CASE WHEN @failed_emails_last24hours > 0 
            THEN 'failed email(s) during last 24 hours; ' ELSE '' END +
         CASE WHEN @failed_email_error > 0 THEN 'failed email test; ' ELSE '' END END;
         

Create SQL Server Agent Job executing PowerShell script

The final solution is to create and schedule a SQL Server Agent Job that executes the following PowerShell script.

The PowerShell script in its order will call the Invoke-SQLCmd cmdlet that runs our T-SQL script on all SQL Servers registered in CMS.

The PowerShell script has the following variables that you will need to configure:

  • $CentralManagementServer - name of the CMS where we get SQL Servers names from
  • $InputSQL - location of the T-SQL Script provided above
  • $OutputCSV - output *.csv file that will have execution results
  • $$SQLCmdVar - variable to pass DBA email to the T-SQL script
$CentralManagementServer = "SQLCMS"$InputSQL = "D:\Scripts\DBMailStatus_check.sql"
$OutputCSV = "D:\Scripts\DBMailStatus.csv"
$SQLCmdVar = "DBA_Email='[email protected]'"

# overwrite output CSV file
$resetCSV = 'start'
SELECT $resetCSV  | Export-CSV ${OutputCSV}

# get all registered SQL Server names from CMS
$Result =  Invoke-Sqlcmd -ServerInstance $CentralManagementServer -Database msdb -Query "select server_name from msdb.dbo.sysmanagement_shared_registered_servers" -ErrorAction Stop

# on each SQL Server run our T-SQL Script to check DB Mail related issues. Output to CSV file
foreach($item in $Result){$Inst=$item.server_name
   
   Try {
      Invoke-Sqlcmd -ServerInstance  ${Inst} -Database msdb -InputFile $InputSQL -Variable $SQLCmdVar -ErrorAction SilentlyContinue -ErrorVariable ProcessError | Where-Object {$_.FinalResult -eq 0}| Export-CSV -Append -NoTypeInformation ${OutputCSV}
   }

   Catch{
      $dt = Get-Date -Format g
      $obj = New-Object PSObject
      Add-Member -InputObject $obj -MemberType NoteProperty -Name Server_Name -Value ${Inst}
      Add-Member -InputObject $obj -MemberType NoteProperty -Name Run_Date -Value ${dt}
      Add-Member -InputObject $obj -MemberType NoteProperty -Name FinalResult -Value "0"
      Add-Member -InputObject $obj -MemberType NoteProperty -Name Notes -Value "!!!Test Failed with other error"

$obj|Select-Object Server_Name, Run_Date, FinalResult, Notes | Export-CSV -Append -NoTypeInformation ${OutputCSV}
      }
   } 

If you want to get notifications for both failed and successful checks you will need to remove the following filter in the PowerShell script:

Where-Object {$_.FinalResult -eq 0}

Create Proxy Account

In order to run the SQL Server Agent Job with the PowerShell script, you need to have a SQL Server Agent PowerShell Proxy with permissions to access input/output files location(s) and permissions to run the T-SQL script provided above on all SQL Servers. This Proxy has to be created on the CMS or another SQL Server where the SQL Agent Job is created (if you run the Job from another server):

USE [master]
GO

-- create credential
CREATE CREDENTIAL [PowerShell_Credential] 
WITH IDENTITY = N'DOMAIN\sql_ps_proxy_forMoninoring', SECRET = N'domain_password_here'
GO

-- create SQL Server Agent proxy for the PowerSHell subsystem
EXEC msdb.dbo.sp_add_proxy @proxy_name=N'PowerShell_Proxy',
            @credential_name=N'PowerShell_Credential', @enabled=1
GO

EXEC msdb.dbo.sp_grant_proxy_to_subsystem @proxy_name=N'PowerShell_Proxy', @subsystem_id=12
GO

The first step of the Job executes the PowerShell Script:

Job Step 1 - Description: Job Step 1 - running PowerShell script as proxy account

The second step of the Job sends an email with output *.csv file (results of the check) attached:

Job Step 2 - Description: Job Step 2 - Send CSV results attached to email
EXEC msdb.dbo.sp_send_dbmail      @profile_name = 'SQLServerAgentAlerts',
      @recipients = '[email protected]',
      @subject = 'Database Mail Daily Health Check',
      @file_attachments = 'D:\Scripts\DBMailStatus.csv'

Here is an example of the output file (filtered):

Output Example - Description: Database Mail check results - ouput file example

Keep in mind that often when you monitor from a specific SQL Server (for example the CMS server) you can miss that this server might have issues itself, so make sure Database Mail works on this SQL Server. In our case, if there are no issues you will get an email regardless of a number of issues. If there are no issues then an empty *.cvs file will be attached. If you don't get the email it means there is something wrong with the job or email on the SQL Server that is running the Database Mail check.

Alternatively, you can check the CSV file manually after the first job's step is succeeded or run T-SQL script on the CMS manually from time to time.

Next Steps
  • Check your environment for failed emails using the scripts 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.


Last Update:


signup 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    Notify for updates 


Send me SQL tips:

    



Learn more about SQL Server tools