This article provides troubleshooting guidance for an issue where the SQL Agent service repeatedly stops responding at regular intervals.
Symptoms
The SQL Server Agent service fails intermittently on a SQL Server Always On Cluster. When the service fails, the following error message is added to the SQL Server Agent log:
[510] SQLAgent failed, dump generated in \<SQL_Instance_Log_Directory\>
[LOG] Exception 29539 caught at line 233 of file sql\mpu\SqlAgent\src\autostrt.cpp. SQLServerAgent initiating self-termination.
Cause
These failures occur if the SQL Server Agent service doesn't retrieve the following registry subkey:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\SQLServerAgent\AutoRegistryRefresh
When the registry subkey retrieval fails, the SQL Server Agent service reports an exception in the log, and then it terminates itself.
Registry subkey retrieval can fail because of the following reasons:
- Antivirus or endpoint protection software that interferes with registry access
- Missing or misconfigured registry keys
- Transient access failures in Windows registry
Solution
To resolve this issue, follow these steps.
Step 1: Verify the registry key
- Start Registry Editor (
regedit.exe). - Go to the following path:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\<InstanceID>\SQLServerAgent. - Check whether the
AutoRegistryRefreshkey exists. - If the
AutoRegistryRefreshkey is missing, create a DWORD (32-bit) Value that's namedAutoRegistryRefresh, and set its value to1.
Step 2: Exclude SQL Server from antivirus scanning
Important: The process of adding an exclusion to your antivirus or endpoint protection software varies by publisher. Check with the publisher of your software for specific instructions.
- Configure your antivirus or endpoint protection software to exclude the common SQL Server processes:
sqlservr.exesqlagent.exeReportingServicesService.exemsmdsrv.exefdlauncher.exefdhost.exe
- Also, exclude the folder where SQL Server is installed. For example,
C:\Program Files\Microsoft SQL Server\.
For more information about how to configure your antivirus or endpoint protection software to work together with SQL Server, see Configure antivirus software to work with SQL Server.
Step 3: Check event logs and dump files
Check whether the logs or the most recent SQL Agent dump file contain any new errors:
- Review the SQL Server Agent logs that are located in:
<SQL_Instance_Log_Directory>\SQLAGENT.OUT\. - Review the Application and system logs in Event Viewer for related errors.
- Analyze the SQL Agent dump file. If multiple dump files are generated, focus on the most recent one.
If no new errors are reported, go to the next step. If new errors exist, repeat the previous steps, and then check the event logs and dump files again.
Step 4: Restart SQL Server Agent service
Restart the SQL Server Agent service in order for the changes to take full effect.
Step 5: Monitor stability
- Monitor the SQL Server Agent logs for any recurrence of the error.
- Verify that no new dump files are generated in the log folder.
Source: Microsoft Learn / IT Pro troubleshooting by Microsoft and contributors. Documentation licensed CC BY 4.0. Code samples are covered by the MIT License. Converted from Markdown, with links normalized and embedded media omitted.
