Welcome to MSSQL Forum!
MSSQL Forum - Discussions about Microsoft SQL Server

You are currently viewing our community forums as a guest user. Sign up or
Having an account grants you additional privileges, such as creating and participating in discussions.

Configuring SQL Server Agent

Discussion in 'Installation and Administration' started by Peter Schmitz, Oct 1, 2012.

  1. Peter Schmitz

    Peter Schmitz Administrator Staff Member

    This morning, when attempting to start SQL Server Agent from within SSMS, it refused to start. When trying to check the properties of the SQL Server Agent to determine which credentials it was supposed to run under, SSMS prompted me with the following error:

    This is an error I never saw before, and I figured I would dedicate a quick post on how to solve it. The error message hints at using sp_configure, and running that revealed that the Agent XPs were disabled:

    AgentXPs 01.png

    I issued a RECONFIGURE to set the option to be enabled, and then attempted to start the agent again:

    AgentXPs 02.png

    AgentXPs 03.png

    But again without luck:

    AgentXPs 04.png

    Attempting to retrieve the properties for SQL Server Agent again threw the afore mentioned error:

    AgentXPs 05.png

    And when then checking RECONFIGURE again, it turns out that Agent XPs had been disabled once again. I re-enabled them once more, and then was at least allowed to check the properties. Some Googling revealed some hints at this error perhaps being related to the log not being accessible to write for SQL Server Agent. I decided to log onto the server itself, and check the folder in question:

    AgentXPs 06.png

    Most of the time, ensuring that the folder in question does exist would address this particular problem, but unfortunately, this was not the case for me. As it turns out, the server in question has been renamed, and we performed some changes on the master database in order to change the collation on the server, which most likely lead to usergroups becoming corrupt in the process.

    The log folder in question is the same folder used by SQL Server to write logfiles into, and both SQL Server and SQL Server Agent ran under the same credentials. Changing the credentials of SQL Server Agent to "Local System" solved the issue, but that was not a situation I was too happy with.

    As a result, I uninstalled SQL Server altogether, and am reinstalling it at this moment. I would rather go through the trouble of a reinstall than somehow hack my way into the system to solve the error.

    But, when you do run into this error, make sure to check your log-folder exists, and that the account used by SQL Server Agent has access to it.

Share This Page

Users found this page by searching for:

  1. SQL Server blocked access to procedure sys.xp_sqlagent_enum_jobs