Recently one of the production servers, the following error was logged several times in the error log.
An attempt was made to send an email when no email session has been established
This server is running SQL Server 2005 and we had configured Operators to be notified for certain errors. The Database Mail was also configured and working fine.
On checking the error log, there was a backup failure just before this error appeared in the log. Hence SQL Server Agent tried to notify the operator but could not.
Now what was the issue? It was because of a new SQL Server Agent setting introduced in SQL Server 2005. In SQL Server 2000 all we have to do was to configure SQL Mail and it will be used by all the components of SQL Server using xp_sendmail. But from SQL Server 2005 onwards, by default SQL Server Agent will not make use of the Database Mail configured on the Instance. In the “Alert System” tab under SQL Server Agent properties, the Mail profile needs to be enabled.
In our case, it was not enabled.
This option was enabled and the SQL Server Agent was restarted. What next? The issue got resolved and the operators started getting the emails!