June 8, 2010 at 4:42 am
Hello All,
It's my first post and I'm hoping that someone may be able to help me with an irritating problem I'm having.
I manage a SQL Server 2005 standard (9.0.4053) Development server hosting around 40 databases, on Windows Server 2003 SP2. However, I'm unable to start the SQL Server Agent service. If I attempt to start SQL Server Agent using SSMS, I get the error:
Unable to start service SQLAgent$SQL2005 on server <server-name>
Additional information:
The SQLAgent$SQL2005 service on <server-name> started and then stopped. (ObjectExplorer)
If I attempt to start SQL Server Agent using Configuration Manager, I get the error:
The request failed or the service did not respond in a timely fashion. Consult the event log or other applicable error logs for details.
The errors in SQLAgent.out are as follows:
2010-06-04 05:48:01 - ! [298] SQLServer Error: 208, Invalid object name 'sys.configurations'. [SQLSTATE 42S02] (DisableAgentXPs)
2010-06-04 05:48:01 - ! [298] SQLServer Error: 208, Invalid object name 'msdb.dbo.syssessions'. [SQLSTATE 42S02]
2010-06-04 05:48:01 - ! [000] Error creating a new session
2010-06-04 05:48:01 - ! [298] SQLServer Error: 208, Invalid object name 'sys.configurations'. [SQLSTATE 42S02] (DisableAgentXPs)
2010-06-04 05:48:01 - ? [098] SQLServerAgent terminated (normally)
The SQL Server Agent is running under a domain account, which is the same account that all the other services are successfully running as (I know this account is active and has the correct password). This domain account is also a local server administrator and has the sysadmin SQL Server role.
If I try to change the account that the SQL Server Agent runs as (for example, to LocalSystem), I get an error:
The process terminated unexpectedly. [0x8007042b]
However, if I then cancel out and re-start Configuration Manager, the account has in fact changed. Setting the account to LocalSystem incidentally does not work either (same errors as above).
To help rule out a local permissions issue, I've tried logging into the server as a local admin, but this didn't work either.
I've seen some references to clusters during my Googling of the above errors, but this server isn't in a cluster.
I've even tried a reboot of the server, which didn't help 😉
Anyone have any ideas or suggestions?
Many thanks,
Innerise
June 8, 2010 at 7:04 pm
Hi, I am not sure if this will help, but check 'Agent XPs' configuration option : sp_configure 'show advanced options', 1;
If option is 0 then you will not be able to use SQL Server Agent.
June 9, 2010 at 2:46 am
Thanks for the reply, magasvs.
I've seen that suggestion a few times and yes I did try that. Unfortunately, 'show advanced options' was already set to 1. I set it to 0 and then 1 again just to see if it would work, but it didn't.
June 9, 2010 at 6:34 am
Did you check value for 'Agent XPs' ? You need to set this option to 1 as well:
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO
June 9, 2010 at 6:49 am
Yes, running your script returns:
Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
Configuration option 'Agent XPs' changed from 1 to 1. Run the RECONFIGURE statement to install.
, but it still doesn't enable me to start the Agent.
June 9, 2010 at 5:43 pm
Something else that is required for the SQL Agent is SQL Server Broker enabled for the msdb database. You can check if it is enabled:
SELECT is_broker_enabled FROM sys.databases WHERE name='msdb'
Also, it looks that some people have similar issues related to the Domain Group Policies: http://www.eggheadcafe.com/software/aspnet/31537821/unable-to-start-agent.aspx
June 10, 2010 at 4:44 am
The scripts returns is_broker_enabled=1, so I assume it's enabled.
I had a read of the link you sent me, but it looks like that only applies to starting the agent service using a
domain user account. I can't even start it using a built-in account 🙁
It's as if the problem is the fact that when I try to change the account that the Agent runs under (using Configuration Manager), the process of setting permissions, etc for that account doesn't complete successfully (hence the error 'The process terminated unexpectedly. [0x8007042b]'). This is even though if I then refresh Configuration Manager, the new account is listed, despite the error.
(Thanks again for the help - I really appreciate it)
June 10, 2010 at 4:55 am
Type services.msc in Run Command
then Services Opens
Then Select The SQL SERVER AGENET ( SERVERNAME )
then click it then Click Start..
June 10, 2010 at 7:44 am
Thanks Dare to Fight...if I try to start it using Services, I get the error:
The SQL Server Agent (SQL2005) service on Local Computer started and then stopped. Some services stop automatically if they have no work to do, for example, the Performance Logs and Alerts service.
June 10, 2010 at 10:33 am
Coincidently this very thing happened to me last week and I finally got it resolved. I was changing the SQL Services to run under a domain service account from localService account and then I couldn’t get SQL Agent to start. I also did all the things that have been suggested in this post with no luck. What I finally did to get it going was within SQL Server’s configuration Manager I completely stopped SQL Server then started it up. Once it was up with a different Process ID I tried starting the SQL Server Agent and it worked. Maybe it would work for you, couldn’t hurt to try…
June 10, 2010 at 9:40 pm
Just wondering if you've verified the objects in the messages below exist in msdb?
2010-06-04 05:48:01 - ! [298] SQLServer Error: 208, Invalid object name 'sys.configurations'. [SQLSTATE 42S02] (DisableAgentXPs)
2010-06-04 05:48:01 - ! [298] SQLServer Error: 208, Invalid object name 'msdb.dbo.syssessions'. [SQLSTATE 42S02] 2010-06-04 05:48:01 - ! [298] SQLServer Error: 208, Invalid object name 'sys.configurations'. [SQLSTATE 42S02] (DisableAgentXPs)
June 11, 2010 at 1:20 am
Hi,
Check Aliases under SSCM.
if you are running the sql agent under domin account, then check that if you have created Alias in SQL Server Configuration Manager.
If not present then create one and try.
June 11, 2010 at 3:15 am
Mary - I have completely re-booted the server to try to fix the problem, so I assume that would have forced the service to get a new process/session ID? I'll arrange a time with the developers to re-start the MSSQL service and will give your suggestion a try.
Beargle - Yes, I've verified that all the objects mentioned in the error exist. The msdb.dbo.syssessions table does exist and running SELECT * FROM sys.configurations returns a long list of configuration values, so I assume they're working OK. The error suggests otherwise, so not sure what the error is complaining about.
chetanr.jain - I'd be happy if I could get the Agent running under a local account, let alone a domain account 🙂 I must admit that I have never had to configure any aliases up until now, but it's worth a try at this point.
June 11, 2010 at 4:44 am
Cracked it :w00t:
I didn't have to create a new alias in the end, but it appears that it was an alias-related issue.
The instance I was having a problem with is a named instance. However, in SSMS > SQL Server Agent > Properties > Connection > Alias local host server, the value was the hostname only (ie default instance)
I changed the value from the default instance to the named instance and I was able to start the Agent service.
Thanks for all your help, guys! Hopefully, this post will help someone else at some point.
June 11, 2010 at 4:49 am
Good. Finally your issue was solved.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply