technology and zen of life

“A heisenbug (named after the Heisenberg Uncertainty Principle) is a computer bug that disappears or alters its characteristics when an attempt is made to study it.”

Get SQL Agent service starting

Today I was struggling with Microsoft’s SQL Server. I could not get the SQL Agent service starting. Maybe my experiences can help you.

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 FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLBinn
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_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERSQLServerAgent]
"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_MACHINESOFTWAREMicrosoftMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERSQLServerAgent]

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.

SQL login

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 [MYSERVERsqlagent] FROM WINDOWS WITH DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english]
GO
EXEC sp_grantlogin [MYSERVERsqlagent]
EXEC sp_addsrvrolemember @loginame = [MYSERVERsqlagent], @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.

 

(Original article first published at my personal weblog.)

 Get SQL Agent service starting

About Evert Mouw

Interested in kinda everything, studied political science, now studying medical informatics. MCSE, Linux enthousiast, and believing that technology is the path to enlightenment ;-) Best RTS game ever is Warhammer Soulstorm (IMHO). Other hobbies include hiking, kayaking, reading and when I have time, trying to have an ant colony in an artificial nest (formicarium).

Leave a Reply

One Response

Email Subscription

Disclaimer

The views expressed on this blog are personal. We do not claim to be a representative voice of the views of any organisation whatsoever. We are not responsible for the content present on the blogs to which we have linked.Views expressed are solely that of the author and does not reflect a collective opinion of contributors.
%d bloggers like this: