Sql-server – SQL Server Agent initiating self-termination

sql serversql-server-2012sql-server-agent

For the past month, one of my servers is facing issues with SQL Server Agent. It stops automatically with below error, which happens randomly:

Sql Agent Output file
[097] Memory leak detected [204 bytes]
Exception 5 caught at line 403 of file e:\sql9_sp2_t\sql\komodo\src\core\sqlagent\src\alerter.cpp. SQLServerAgent initiating self-termination

I googled this error but did not get much info about it. Both SQL Server and Agent are running with the same domain account; however, the issue is only with the SQL Server Agent service.

I am also unable to see the SQL Server Agent service in SQL Server Configuration Manager. I checked the SQL Server error log, and Windows Event Viewer logs, but did not see any suspicious activity.

My memory settings are configured correctly. The server has 32GB RAM and SQL Server is assigned to use max memory of 26GB.

System details:

  • SQL Server 2012 SP2 Std Edition 11.0.5058 64 bit
  • Windows Server 2008 R2 Ent Edition 64 bit
  • Dedicated to SQL Server (no other apps run on the server)
  • The server was upgraded a few months ago from SQL Server 2005 EE to 2012 SE.
  • We are not using an Events Forwarding Server.

I checked the msdb database as well. Ran checkdb on it and it completed successfully without any errors. I need your help in fixing two things:

  1. To fix the random stopping of SQL Server Agent service
  2. To add SQL Server Agent service in SQL Server Configuration Manager

We have third-party software for monitoring, so we get notified immediately if the agent service stops. For now we are just restarting the service. But we want to find the root cause.

I ran the following query suggested by Sean Gallardy and got 95 rows back. I don't know how to interpret the result.

select * 
from sys.dm_os_loaded_modules 
where name not like '%microsoft%'

Unfortunately I cannot post the results for security reasons.

Until I find the solution to this problem I have added retry attempts to the SQL Server Agent so that it can restart automatically when it gets stopped.

Best Answer

Since you recently upgraded a few months ago from SQL Server 2005 EE to 2012 SE, you should save off and clear your event logs because SqlAgent may crash when it sees Event Log IDs from an older build of SQL Server. For example, the Agent may crash because it reads an event logged by a SQL Server build earlier than the current build, which only contains data with ONE insertion string and the agent will try to format it using the latest resource information that requires TWO strings. This would lead to a brief memory leak just before it shuts down since a buffer is allocated for the string that it is trying to print into when the crash occurs.

As far as the agent service not listed in configuration manager, there are multiple possibilities: https://support.microsoft.com/en-us/kb/941823

Ensure you are running 2012 Configuration Manager

You can try rebuilding the WMI Repository: https://blogs.technet.microsoft.com/askperf/2009/04/13/wmi-rebuilding-the-wmi-repository/

Related Question