The Agent XPs Option in SQL 2005 is disabled when i attempt to start SQL Server Agent

Question

Post reply

The Agent XPs Option in SQL 2005 is disabled when i attempt to start SQL Server Agent

Maarten Takens

SSC Eights!

Points: 844
More actions
March 13, 2008 at 9:22 am

#187860

At first when i attempted to create a Maintenanceplan i received the error message wich stated that it could not be done because the 'Agent XPs' component was turned off
I have enabled this option with the following Query:
sp_configure 'show advanced options', 1
reconfigure
sp_configure 'Agent XPs', 1
reconfigure
to be sure i ran an sp_configyre to see if the option was enabled , this is the result
Name Minimum Maximum Config_value Running Value
Agent XPs 0 1 1 1
This indeed enables the Axent XPs option and lets me create a Maintenance plan but after i finished the maintenance plan, however when i start the SQL Server Agent it starts en directly stops with the following message:
The SQL Server Agent (MSSQLSERVER) service on local computer started and stopped . some services stop automatically if they have ni work to do , for example the Performance Logs and Alerts service.
When i go back to the SQL management studio and run a sp_configure i get the following result:
Name Minimum Maximum Config_value Running Value
Agent XPs 0 1 0 0
it has been changed back to 0 again .. i have installed SQL sp 2.0 but that did not help me ..
Does anyone of you recognize this problem please help me

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

MarkusB SSC-Dedicated Points: 37370 More actions · Answer 1

In SQL Configuration Manager set the SQL Agent Service to start automatically. This should resolve your problem.

[font="Verdana"]Markus Bohse[/font]

Maarten Takens SSC Eights! Points: 844 More actions · Answer 2

Hello Markus,

I already checked that , and it is configured to start automatically .The problem is that when the sql server agent tries to start it turns this value for the AgentXPs back from 1 to 0 and then the service stops immediately reporting no error but it states:

The SQL Server Agent (MSSQLSERVER) service on local computer started and stopped . some services stop automatically if they have ni work to do , for example the Performance Logs and Alerts service.

I also checked if the SA_SQL user under witch all the SQL services are running have proper rights ( Local admin and no Policys are involved here )

Adam Bean One Orange Chip Points: 26631 More actions · Answer 3

Check your application event viewer ... this is usually caused by a service running with invalid credentials, or the agent improperly configured (logs are pointing to an invalid directory for example).

Maarten Takens SSC Eights! Points: 844 More actions · Answer 4

Hi the problem has been resolved , i tried to start the SQL Agent from the command line and got an Error 6 wich lead me to the register and here i found out that the path to the ErrorLogFile was pointing to a wrong location ( HKEY_LOCAL_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent)

after i changed the path i could start up the SQL Server Agent normally.

Thanks for your help

Regards

Maarten

manoj_Ms Valued Member Points: 55 More actions · Answer 5

hi....

can u tell me wht changes u made in registry...

Maarten Takens SSC Eights! Points: 844 More actions · Answer 6

Hello,

In the HKEY_LOCAL_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\SQLServerAgent i had to change the Value "ErrorLogFile" ( the path to the Errorlogfile was wrong , this was the reason he could not find the logfile and aborted the SQL Server Agent )

If this is the case also check the value "WorkingDirectory" to verify that the path here is correct.

Regards

Maarten Takens

William N. Matsoukas Valued Member Points: 61 More actions · Answer 7

I had the same issue. When I attempted to start SQL Agent from the command line, no error was returned, however the problem was that the log file directory assigned to the SQL agent did not exist. I simply created the directory.

You can also assign SQL Agent another log directory using SQL Management Studio. No need to go into the registry. You may have to use sp_configure to set the Agent XPs in order to access the SQL Agent property page.

Bill

sqlnoobie Mr or Mrs. 500 Points: 528 More actions · Answer 8

Hi: I'm having the same problem...SQL Agent won't start. I've tried a domain user account, the domain administrator account, and the local system account, but none of those works.

I was able to enable Agent XPs, but it still won't start.

I checked the log path both in SMS and in the registry, and they are both correct.

If I try to start it from the "services" CP, I get:

"Could not start the SQL Server Agent (MSSQLSERVER) service on Local Computer.

Error 1058: The service cannot be started, either because it is disabled or because it has no enabled devices associated with it."

Neelima-620258 SSC Enthusiast Points: 102 More actions · Answer 9

Hi

could you please let me know how to change the log file path in the registry. I changed the path and I am getting the same error after I restarted the services. I don't know how to change the log file path back to normal. thanks!

Adam Bean One Orange Chip Points: 26631 More actions · Answer 10

kumaripaleti (9/26/2008)
Hi
could you please let me know how to change the log file path in the registry. I changed the path and I am getting the same error after I restarted the services. I don't know how to change the log file path back to normal. thanks!

The log file path? You mean for the SQL Agent?

EXEC msdb.dbo.sp_set_sqlagent_properties @errorlog_file=''