SQL Agent Alert System Error: [264] An attempt was made to send an email when no email session has been established

Today, I received an email from the development team complaining why they have not received any of the SQL Agent job notification emails today. Upon checking the SQL Agent error log, I see the following error message:

[264] An attempt was made to send an email when no email session has been established.

SQLAgent_ErrorLog

To investigate the issue, I followed the steps below to verify that SQL Server Agent Alert System is configured correctly and is associated with the correct Database Mail profile:

  • Right Click on SQL Server Agent, Select properties.
  • Select Alert System.
  • Select Enable mail profile.
  • Select OK.

I also successfully sent a test email using this Database Mail profile.

Although, I verified above settings and sent test emails using Database Mail, SQL Server Agent Job was still unable to notify the SQL Server Agent Operator. At this point, I decided to Google this error message because I was not sure why this error is occurring. After digging around online, I realized I just need to restart the SQL Server Agent service. After I restarted SQL Server Agent service, SQL Server Agent job notifications started working again.

DML Triggers to track SQL Server Agent Jobs creation, deletion and modification events

SQL Server Agent jobs are crucial to any SQL Server environment, as they are used for scheduling and performing critical business and operational tasks. For example, I’ve got several hundred SQL Server Agent jobs that are scheduled to run on a daily, weekly or monthly basis on our various SQL Servers. These jobs are very critical, and as a database administrator, my responsibility is to ensure that these jobs are running every day, or on a weekly, or monthly basis and no unauthorised changes have been made to these jobs. I also need to know, who and when a SQL Server Agent Job is created, deleted or modified on my SQL Server? Obviously this is not an easy task to perform manually and there are not many options available in SQL Server to monitor such kind of activity. As a result, I have decided to build my own monitoring solution that automated this task with the help of DML triggers, which helps me to monitor SQL Agent Job modification activity in real-time.

Checkout my article here, which shows step-by-step implementation of this monitoring solution, which performs real-time monitoring of SQL Server Agent Jobs creation, modification and deletion activities.

This article is published on SSWUG.org.

Unable to recycle SQL Agent log: SQLServerAgent Error: 32.

Today the maintenance job that recycles the SQL Agent error log failed on one of our production SQL Server with the following error message:

Message: Executed as user: MyDomain\_svcSQLServer. SQLServerAgent Error: 32. [SQLSTATE 42000] (Error 22022). The step failed.

This job uses [dbo].[sp_cycle_agent_errorlog] procedure that resides inside msdb, which enables you to cycle the error log files without stopping and starting the server. When I tried to execute the [dbo].[sp_cycle_agent_errorlog] manually in SQL Server Management Studio it failed with following error message:

Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: 32.

I researched this issue and found that the SQL Agent log file was locked by other process. I killed the process that was locking the SQL Agent log file, restarted the SQL Server Agent service and this time job successfully recycled the SQL Agent error log.