Get SQL Agent service starting

(Dutch intro) Vandaag ben ik flink aan het stoeien geweest met Microsoft’s SQL Server. Als de SQL Agent niet wil starten, en geen error logs achter laat, dan kan de zoektocht beginnen… De rest van het artikel is vooral geschreven voor IT professionals of liefhebbers die hier mogelijk tegenaan lopen en misschien iets aan mijn ervaringen hebben.

Introduction

Sometimes the SQL Agent service will not start, and it is quite hard to get the cause of this when the Error log doesn’t show much. The hints below were gathered from various websites and online forums, and some of them were tested with a Microsoft SQL Server 2008 R2 stand-alone instance running on Windows 7.

General testing

To get some idea of what is wrong, do this in a command shell:

C:
cd C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Binn
SQLAGENT.EXE -c -v

Error when editing SQL Agent settings

When in the SQL Server Configuration Manager, you might get an error like “the process terminated unexpectedly” after changing the SQL Agent settings.

Check the Windows Event log, Security. If there is a Event ID 6281, maybe with a file called l3codeca.acm, then maybe you are using Remote Desktop (RDP) and you have audio forwarding enabled. Disable it.

ErrorLogFile location

A wrong ErrorLogFile location could also result in an error like “the process terminated unexpectedly” after changing the SQL Agent settings in the SQL Server Configuration Manager.

The ErrorLogFile registry key must point to an existing file location.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent]
"ErrorLogFile"="C:\\MyBigDataStore\\MSSQLSERVER\\MSSQL10_50.MSSQLSERVER\\MSSQL\\Log\\SQLAGENT.OUT"

Registry key permissions

The Windows user account that is being used to start the SQL Agent service must have full rights on the same registry key:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\SQLServerAgent]

I experienced some problems even after setting the permissions right. Consider giving the “Users” group (all users) full permissions (read & write). If you don’t like that, you could at least try that to see whether it’s a registry key permission issue.

Windows Groups

When the user being used to start the SQL Agent service is not a member of the local Administrators group or not the LocalSystem account, then you need to be sure that the user is a member of the SQLServerSQLAgentUser$SERVERNAME$MSSQLSERVER group. Note that you need to set SERVERNAME to your specific server name.

Furthermore, this group might need to have permissions on various folders, and also needs a SQL login on the SQL database with sysadmin permissions.

The user being used to start the SQL Agent service needs a matching SQL login on the SQL database with sysadmin permissions.

If you migrate the master database to another computer or a new domain, then the SIDs of the Windows accounts will probably be different, even although the usernames and group names are the same. You need to create new SQL logins and map them to proper SQL user permissions.

For example, when using a Windows user “sqlagent” to start the service, you need:

CREATE LOGIN [MYSERVER\sqlagent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC sp_grantlogin [MYSERVER\sqlagent]
EXEC sp_addsrvrolemember @loginame = [MYSERVER\sqlagent], @rolename =  'sysadmin'

Virtual Host or Alias

When in clustered mode, then the virtual host or alias name for the SQL Agent service needs to match the one of the SQL Server service. Check the properties in the SQL Server Configuration Manager.

Enable Agent XPs

This seems to be needed in some scenarios.

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

Broker

Some broker functionality was mentioned on a forum. It was rumored that the broker should be enabled. You can check that with:

SELECT is_broker_enabled FROM sys.databases WHERE name='msdb'

But I’m not sure whether this is needed.

Result

(Also published on TechMonks.)

Deze blogpost werd in december 2022 overgezet van WordPress naar een methode gebaseerd op Markdown; het is mogelijk dat hierbij fouten of wijzigingen zijn ontstaan t.o.v. de originele blogpost.