data-konzept

'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.

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.

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.

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
——————————'...

http://blog.sqlauthority.com/2016/02/29/sql-server-unable-to-start-sql-server-agent-failed-to-initialize-sql-agent-log/