SQL Mail does not work anymore

Product:

Microsoft SQL server 2016

Issue:

SQL agent can not send mail. There is no error in windows event log, and no errors in SQL server log.

Solution:
Install NET Framework 3.5 on your SQL server.  Download from here:

https://www.microsoft.com/en-us/download/details.aspx?id=25150

SQL server 2016 demands Net Framework 3.5 for the mail function to work.

Check if NET Framework is installed on your server in folder:

C:\Windows\Microsoft.NET\Framework64

Folder is missing from the SQL server where SQL Mail is not working.

On a SQL server, where mail function is okay, the v3.5 folder exist.

You can also test this by go to the SQL server, start a command prompt, and go to folder

C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Binn

Run DataBaseMail.exe

If you get a error that Net Framework can not be installed, then this is the problem.

Please note that the windows account that run the SQL Server Agent (MSSQLSERVER) must have access rights to the Net Framework 3.5 folder and its files.

You can check if SMTP mail host is working by run below PowerShell command:

Send-MailMessage -To "your.mail@company.com" -From "noreply@company.com"  -Subject "Test mail powershell" -Body "Some important plain text!" -Credential (Get-Credential) -SmtpServer "smtp-servername" -Port 25

If that works, then the SMTP and firewall is correct. Then you need to check SQL servers settings;

SELECT [sysmail_server].[account_id],

       [sysmail_account].[name] AS [Account Name],

       [servertype],

       [servername] AS [SMTP Server Address],

       [Port]

FROM [msdb].[dbo].[sysmail_server]

     INNER JOIN [msdb].[dbo].[sysmail_account] ON [sysmail_server].[account_id] = [sysmail_account].[account_id];

Above gives you the SMTP setup

EXEC msdb.dbo.sysmail_help_queue_sp @queue_type = 'Mail';
Above will show the status of the mail log
SELECT TOP (1000) [profile_id]
,[name]
,[description]
,[last_mod_datetime]
,[last_mod_user]
FROM [msdb].[dbo].[sysmail_profile]

Above will show the SQL Mail profiles.

More information:

Troubleshooting Database Mail Failures

https://www.sqlshack.com/configure-database-mail-sql-server/

Check version of SQL server : https://sqlserverbuilds.blogspot.com/