February 5, 2008 at 1:38 pm
I have a server at a client's location where the Server Agent stops (usually for the same reason) and does not restart itself. The option to "Auto restart SQL Server Agent if it stops unexpectedly" is checked. According to the event log, the reason it stops is:
SQLServerAgent could not be started (reason: Unable to connect to server '(local)'; SQLServerAgent cannot start).
This is running SQL Server 2000 with SP3 on Server 2003 Standard Edition with SP2.
I know enough to be able to get around Enterprise Manager, but I am not sure what I should check to resolve this problem. I would appreciate any suggestions. Please let me know if you need any further information.
Thanks,
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
February 5, 2008 at 3:00 pm
Try checking the following:
- under Control Panel, Admin tools, services - double-check to see what the service account is for SQL Agent. That account needs to be able to enabled, not have its password expired, and needs to have access to logging in to SQL Server (any of those might cause that error). Try seeing if you can log in to Query analyzer using its credentials.
- we had issues with servers at times losing their AD context, and for all intents and purposes, the server couldn't/wouldn't talk to anyone else, since AD auth would fail.
- double-check that nothing odd has happened to the DNS on that server. from QA - type in select @@servername, and double-check that what it returns really is the server name and instance. In some cases - that gets fouled up, and agent can't "resolve" who it is trying to talk to, in which case that select would return NULL.
- if memory resources are really low, it might not be able to start as well.
Most of these things are fixed with a cold-boot of the server.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
February 5, 2008 at 3:26 pm
permission problem. check for regidtry, folder and system permissions for the service account.
Cheers,
Sugeshkumar Rajendran
SQL Server MVP
http://sugeshkr.blogspot.com
February 6, 2008 at 8:25 am
Thanks for your responses. The account is enabled with no expiration on the password. There has been no problem logging into QA with this account that I know of (I have been able to do that at anytime). Also, it looks like this account has all the required permissions. I checked in Active Directory Users and Computer. Is there anywhere else I need to check? @@servername returns the correct server name.
I just spent a little time looking at the sequence of events in the event viewer. From what I can tell, this problem has happened every time the server has had to restart because of system shutdown.
On the recovery tab (in services) for the SQL Agent all the computer response if the server fails are set to "Take No Action." Should I change these to "Restart the Service?"
Thank you,
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
February 6, 2008 at 8:45 am
SQLAgent should be dependent on SQL Server. If it's not, then it might be trying to connect before SQL Server itself is up.
You can try changing the SQLAgent account in EM to your account and then back to the service account. This will add the proper permissions.
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
February 6, 2008 at 9:10 am
Steve - thanks. Per your advice I have changed the account to mine and then back to the service account (system). I can't restart this server now, but when I can I will see if this has helped. I will definitely update this post with the outcome.
Thanks,
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
February 7, 2008 at 2:57 am
The SQL Agent option 'auto restart if service stops unexpectedly' performs the same function as the Service applet auto restart function, but does it in a different way.
In order for Agent to do the auto restart, the Agent service account must have local admin authority (this applies also to SQL 2005 & 2008). If you configure the Service applet to do the auto restart, then you can leave these options blank in EM (SSMS for 2005, 2008) and the Agent account does not need local admin authority for this function. The Service applet method also gives you more flexibility in getting the service restarted.
Note that in SQL 2000 the Agent account may need local authority for other reasons, especially if you have jobs owned by non-sysadmin accounts.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 7, 2008 at 7:25 am
EdVassie (2/7/2008)
The SQL Agent option 'auto restart if service stops unexpectedly' performs the same function as the Service applet auto restart function, but does it in a different way.In order for Agent to do the auto restart, the Agent service account must have local admin authority (this applies also to SQL 2005 & 2008). If you configure the Service applet to do the auto restart, then you can leave these options blank in EM (SSMS for 2005, 2008) and the Agent account does not need local admin authority for this function. The Service applet method also gives you more flexibility in getting the service restarted.
Note that in SQL 2000 the Agent account may need local authority for other reasons, especially if you have jobs owned by non-sysadmin accounts.
Ed,
Which way has priority (Agent or the Service Applet)? In other words, is it possible to have both set or would that be a problem? I wasn't able to restart the server yesterday, but I hope to be able to late this afternoon so I can try these things. Fortunately, this server is for an office that closes at 3 each day (I wish I could leave then...)
Thanks,
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
February 7, 2008 at 10:10 am
Ian,
I have not tried having both of these specified at the same time. It is possible that both mechanisms would try to start Agent, resulting in at least 1 of the starting instances failing. (They may both detect Agent is running then stop, or 1 instance may complete its startup while the other gives an error and fails.)
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
February 8, 2008 at 7:12 am
I have tried restarting the server after changing the account (per Steve) and that didn't have any effect. I have also restarted after setting the services to restart the service (Per Ed) also not solving the problem. That time both the service and the Agent itself were set the restart the agent. Tonight I will try it with only the service set the restart the agent.
Currently the service is set to restart the service after 1 minute. I might try increasing that time to give the server a chance to finish loading. Do you think that would make any difference?
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
May 29, 2008 at 7:54 am
Update:
It looks like either one of the changes I made has worked, or someone else from my office changed something (I like the idea that I can take credit for it ;)). This problem has not occurred since early April and I know the server had been shut down since then (this morning being the latest).
Ian.
"If you are going through hell, keep going."
-- Winston Churchill
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply