SQL SERVER – Unable to start SQL Server Agent – Failed to Initialize SQL Agent log

My friend was researching with SQL Agent parameters and after sometime he called me and told that he is not able to start SQL Agent service. Here was the error in SSMS which is about failed to initialize a SQL Agent log.

SQL SERVER - Unable to start SQL Server Agent - Failed to Initialize SQL Agent log sqlagent-01

Text is below:

TITLE: Microsoft SQL Server Management Studio
——————————
Unable to start service SQLSERVERAGENT on server SQLSERVER2016. (mscorlib)
——————————
ADDITIONAL INFORMATION:
The SQLSERVERAGENT service on SQLSERVER2016 started and then stopped. (ObjectExplorer)
——————————
BUTTONS:
OK
——————————

I asked to check SQLAgent.out file in the same folder which has a SQLERRORLOG file, but he said that nothing was there. Then looked around in event log and found below.

Solarwinds

Log Name:      Application
Source:        SQLSERVERAGENT
Date:          2/23/2016 12:35:14 AM
Event ID:      324
Task Category: Alert Engine
Level:         Error
Keywords:      Classic
User:          N/A
Computer:      sqlserver2016
Description:   Failed to initialize SQL Agent log (reason: The system cannot find the path specified).

At this point, I told my friend that we need to find the path setting for SQL Agent log and fix it. But we were unable to open the properties of management studio with below error.

SQL SERVER - Unable to start SQL Server Agent - Failed to Initialize SQL Agent log sqlagent-02

TITLE: Microsoft SQL Server Management Studio
——————————
Cannot show requested dialog.
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
—————————–
SQL Server blocked access to procedure ‘dbo.sp_get_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’, search for ‘Agent XPs’ in SQL Server Books Online. (Microsoft SQL Server, Error: 15281)
——————————
BUTTONS:
OK
——————————

As per error message, we have enabled ‘Agent XPs’ parameter. For this is an advanced setting, we need to enable “show advanced options” before changing the value for “Agent XPs”

sp_CONFIGURE 'SHOW ADVANCE',1
GO
RECONFIGURE WITH OVERRIDE
GO
sp_CONFIGURE 'AGENT',1
GO
RECONFIGURE WITH OVERRIDE
GO

Checked properties and found below

SQL SERVER - Unable to start SQL Server Agent - Failed to Initialize SQL Agent log sqlagent-03

Since there was no D drive (it was a USB which he removed), we need to change that. Here is the command to fix that.

USE [msdb] GO
EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N'
C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLAgent.out'
GO

Here is the location in the registry

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL13.MSSQLSERVER\SQLServerAgent

Highlighted piece is the combination of SQL version and instance name. MSSQL13 stands for SQL Server 2016. MSSQL12 is for SQL Server 2014 and so on.

Once we change the path to a valid location, SQL Server Agent was started.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Solarwinds
, ,
Previous Post
SQL SERVER – AlwaysOn Availability Group Stuck in Resolving State For Long time
Next Post
SQL SERVER – Instance Definition Working with Oracle and SQL Server

Related Posts

14 Comments. Leave new

  • Hi Pinal,
    Is it necessary to configure these parameters being an ‘sa’ user or any non-sysadmin user can also configure them? I’m getting an error when trying to configure from a non-sysadmin login.
    Is it possible to start the SQL Agent with these parameters without being an ‘sa’ user?

    Reply
  • Hi Pinal,

    I am getting this kind of error, could you please help me in this issue.

    Msg 15123, Level 16, State 1, Procedure sp_configure, Line 79
    The configuration option ‘agent_xp’ does not exist, or it may be an advanced option.

    Valid configuration options are:

    Reply
  • Help me

    I have sqlserver 2014 standar, uninstall all components of visual studio 2012 and sqlagent and failed to start , shows me the message of this post . I do not know what to do. Help me

    Reply
  • Worked for me once I changed the location of errorlog.out to a valid location. Thanks!

    Reply
  • Thank Pinal! This was exactly what was wrong with the SQL Server Agent on our Development server.

    Reply
  • For my instance of SQL 2017 the “N” here: EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=N Appeared to be a typo and prevented the agent on my system from starting. After removing the N, it started and life went on. Good article, good info. Thank you.

    Reply
  • Elias Krontris
    May 18, 2018 2:05 pm

    Thanx Pinal … this solution was super accurate for my case!

    Reply
  • Hi, even i also got the same message The SQLSERVERAGENT service on SQLSERVER2016 started and then stopped.
    , then i google the error message & tried a lot, finally i found the account (NT SERVICE\SQLAgent$Instance) doesn’t have sysadmin role, after enabling sysadmin this agent service started running.

    But when i check in windows event log got some message a not resisted DCOM & some registry keys have mot permission such like..

    Security->logins->(NT SERVICE\SQLAgent$Instance Name)-> double click-> server roles -> select sysadmin

    OR

    Server roles -> sysadmin -> double click -> add -> (NT SERVICE\SQLAgent$Instance Name) ->ok

    Reply
  • piyush dwivedi
    July 25, 2018 3:56 pm

    Thanks a lot Sire… this was really working like a charm. .for me path was still same but still runing last command – EXEC msdb.dbo.sp_set_sqlagent_properties…… with the same path resolved issue. Thanks a million.

    Reply
  • I have SQL server 2012…I try with all query (query working) …all step but until now SQl agent didn’t start

    Reply
  • Awesome tip, thanks Sir.

    Reply
  • Thank you for this information, Pinal!

    Reply

Leave a Reply

Menu