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:
I issued a RECONFIGURE to set the option to be enabled, and then attempted to start the agent again:
But again without luck:
Attempting to retrieve the properties for SQL Server Agent again threw the afore mentioned error:
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:
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.
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:
I issued a RECONFIGURE to set the option to be enabled, and then attempted to start the agent again:
But again without luck:
Attempting to retrieve the properties for SQL Server Agent again threw the afore mentioned error:
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:
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.