SQL Server Agent failing to start with the error “StartServiceCtrlDispatcher failed (error 6)”


To totally unlock this section you need to Log-in


Login

he following issue on SQL Server can lead SQL Server Agent to fail to start. Before this there can be some problems with the disks on the server and the databases after restoring from backups.

Checking the logs you could see that:

  • System Event Log – No errors.
  • SQL Server Error Log- No errors.

The attempts to start the service from services.msc and SQL Server Configuration Manager fail with a generic error message. Hence we can take the SQL Server Agent service’s binary path from the service Properties.

SQL Server Agent failing to start with the error "StartServiceCtrlDispatcher failed (error 6)"

Then typing the command "...Binn\SQLAGENT.EXE” -i MSSQLSERVER" should output the following yellow error in command prompt.

SQL Server Agent failing to start with the error "StartServiceCtrlDispatcher failed (error 6)"

The error message should be "StartServiceCtrlDispatcher failed (error 6)". Obviously the message itself is not so helpful. Trying to start sqlserveragent.exe with the “-c”, parameter which indicates SQL Server Agent is running in console mode, will give us some more details:

...Binn\SQLAGENT.EXE” -i MSSQLSERVR -c

SQL Server Agent failing to start with the error "StartServiceCtrlDispatcher failed (error 6)"

SQL Server Agent is trying to rename D:\Data3\SQLAGENT.OUT to D:\Data3\SQLAGENT.1. It is failing to start since the file doesn’t exist because the drive isn’t there.

Now, time to change the SQL Server Agent Log path. Trying executing the following command could fail:

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N’C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT’

Msg 15281, Level 16, State 1, Procedure sp_set_sqlagent_properties, Line 0
SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling ‘Agent XPs’, see “Surface Area Configuration” in SQL Server Books Online.

NOTE: obviously we could turn on the Agent XPs, run the above query and then turn off them again (by using sp_configure 'Agent XPs', 0;) by using the following statements:

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

The only option left was to modify the SQL Server Agent Error Log path in the Registry. Navigated to the following registry key and modified it to point to the correct path.

SQL Server Agent failing to start with the error "StartServiceCtrlDispatcher failed (error 6)"

As expected now the SQL Server Agent will start successfully.