Auto restart SQL Server Agent
-
How is <subj> supposed to work? I tried killing the process in task manager, and it didn't restart...
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Can you post more information? I am not sure what you mean.
Also, you should be able to go to the Services item in control panel and stop and start SQLAgent.
Steve Jones
-
OK, what I was asking about is the supposed feature of SQL Server Agent to restart itself if it is unexpectedly stopped. To see what I mean, right-click SQL Server Agent in SQLEM, properties, tab Advanced. Here is two checkboxes, one for having SQL Server Agent automatically restart SQL Server if it stops unexpectedly, and one for restarting SQL Server Agent if it stops unexpectedly.
The first one works great, I killed the SQL Server process in Task Manager and it was back up and runnung a few moments later. But it's the second one I'm concerned with. I can't see how it is supposed to work. How would Agent be able to restart itself when it is dead? And as I expected, it didn't restart if I killed it in Task Manager.
So, what I'm looking for is if anyone has had any expereince with this feature, how it is supposed to work and finally if and in what way you are monitoring the services.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
I did exactly what you tried and it worked fine for me. Could not kill sqlagent.exe from task manager though had to use the kill command but it came right back up with a different process id after I killed it. I am surprised that task manager allowed you to end the process this dosen't seem right to me? Anyone else
-
I was able to kill an instance (SQL 7's Agent on a Win 2K Pro box) of SQL Agent, but like Leon said, it came right back. I, too, am surprised that I could kill the process through Task Manager, but it happened.
Books Online for SQL 2K has the following (SQL Server Agent, startup account):
quote:
If you are running SQLServerAgent in an account other than a Windows NT 4.0 or Windows 2000 domain account, the following will occur:- CmdExec and ActiveScripting steps of jobs owned by nonsysadmins will fail.
- The autorestart features in SQLServerAgent will not work.
- On-idle job schedules will not allow the job to run.
You didn't mention how SQL Server Agent was set up to run. I know in my case it's running under a domain account, so mine autostarted without issue. Could this be your culprit?
As for how it autostarts, that's not something I have found any info on.
K. Brian Kelley
K. Brian Kelley
@kbriankelley -
SqlAgent runs as a NT service so the operating system is responsible for restarting the agent if it fails. In fact if you go to Administrative Tools --->Services ---> and right click on SQLServerAgent and choose properties, then click the recovery tab. There is an option for First Failure: Chose: "Restart the Service". This option also works fine for me even when I removed the previous option in Enterprise Manager. Try it this way and see what happens.
-
OK, I've continued testing this and I've come up with the following:
The solution Leon presented works great. I kill the service (using either the kill utility or by ending the process in Task Manager, why I am able to do this I am not sure of, maybe it's because I'm logged on as the local administrator) and after the specified interval (60 s default) the service kicks back up. Just as Leon said, it doesn't even matter if the 'Autorestart Agent'-option in SQLEM is checked or not. This is the functionality I was looking for, so in that regard the question is answered.
But I'm still interested in why I can't get the 'internal SQL Server Agent'-way of doing it to work, so I changed the setting in Services to 'No Action' again. The server I've been testing this on is a Win 2K Advanced Server SP1, SQL Server 2000 Enterprise Edition (SP1). SQL Server & SQL Server Agent are both running under the same domain account. I've been logged on as both the local administrator and the domain account SQL Server (& Agent) uses, both with the same results: when the process is killed (using kill or end process in Task Manager) it stays dead. Service Control Manager notices it and places an error in Event Viewer System Log, but since it's not set to restart the service it just notices it.
After this I tried it on my own workstation (Win2K Pro SP1, SQL2K Developer Edition (without SP1)), both running under the same domain account as above. Service Manager set to take no action, but checkbox checked in SQLEM. Killed the process (either kill.exe or end process) and what-do-you-know, it just jumped right back up again, and without waiting 60 seconds. Looking at the events in Event Viewer Application Log (below), I found something interesting. The event source for the monitoring functionality is MSSQLSERVER. The event from Service Control Manager in the System Log, noticing the unexpected shutdown of SQL Server Agent is still logged, but it is MSSQLSERVER that restarts SQL Server Agent. OK, now I've got the answer to how the auto-restart function works. Only thing left now is to figure out why it works for you guys and my workstation, but doesn't work on my server. (continued below event messages....)
Event Type:Error
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:2001-07-24
Time:10:24:03
User:N/A
Computer:MyWorkstation
Description:
SQLServerAgent Monitor: SQLServerAgent has terminated unexpectedly.
Event Type:Warning
Event Source:MSSQLSERVER
Event Category:(2)
Event ID:17052
Date:2001-07-24
Time:10:24:03
User:N/A
Computer:MyWorkstation
Description:
SQLServerAgent Monitor successfully restarted SQLServerAgent after SQLServerAgent terminated unexpectedly.
Event Type:Information
Event Source:SQLSERVERAGENT
Event Category:Service Control
Event ID:101
Date:2001-07-24
Time:10:24:05
User:N/A
Computer:MyWorkstation
Description:
SQLServerAgent service successfully started.
OK, now I started thinking "What differs between my workstation and the server?". Besides the obvious that it's a workstation vs server, sql ent ed vs sql dev ed. The one thing I could think of that could possibly be causing this problem is that my server is running as a named instance. Could this be causing the problem? Perhaps. Maybe it's a bug in the 'SQLServerAgent Monitor' functionality, occurring because the agent service is now called SQLAgent$InstanceName instead of SQLSERVERAGENT.
But, this theory doesn't seem correct, as I tried to verify this by installing a Win2K Server, SQL2K Enterprise Edition (SP1) with (only) default instance and tried it there. Nothing happened... Using the solution Leon presented it worked though (of course). So, does anyone have a clue as to what I should look for, settings that might be different etc?
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 07/24/2001 10:23:36 AM
-
I assume you have the default W2K settings? Verify this. Be sure no one has changed items on the server.
Also, is SQL running as a local admin? This might be an issue when preforming service restarts.
No other ideas other than check other software. Anyhting else non-standard on the server? If it doesn't work, I'd look at contacting MS and opening a case. It may be a bug.
Steve Jones
-
As default as I can see. Anything special you would look for? The server is a test server that I use to try stuff with (right now it's autorestarting the services
) and I've installed it myself. No special changes made, and the only non-standard (for a SQL box) software running is IIS, but it is not really used at all. SQL Server is running as a domain account, and I even tried adding this account to the local administrators group, but this didn't change anything. I'll keep looking at it a while, then probably contact MS.As a side note, do you usually have the domain account that SQL Server uses as a member of the administrators group?
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
I pulled the following from MSDN:
SQL Server Agent monitors itself and the Microsoft® SQL Server™ service.
Self-Monitoring
SQL Server Agent starts the xp_sqlagent_monitor extended stored procedure (SQL Server Agent Monitor) to monitor the SQLServerAgent service to ensure that it is available to execute scheduled jobs, raise alerts, and notify operators. If the SQLServerAgent service terminates unexpectedly, the SQL Server Agent Monitor restarts the service.
It appears that the xp_sqlagent_monitor extended stored procedure controls the functionality you are looking for: Try running the procedure from query analyzer and see if it runs (it works for me):
execute xp_sqlagent_monitor 'START','',1
Also it looks like the dll that controls this is xpStar.dll
my version is as follows:
located at: C:\Program Files\Microsoft SQL Server\MSSQL\Binn
version is: 2000.80.194.0
the last modified date is:Sunday, August 06, 2000, 2:51:30 AM
-
I usually setup MSSQL and SQLAGent to run under a domain account that is a member of some global group so I can assign rights. The account is a local admin, but not a domain admin.
Steve Jones
-
Hmm...interesting. I totally forgot about my workstation running SQL Server 8.00.194 vs. my server 8.00.384 (although I did write it in one of the prior posts in this thread
). I have the same version of xpstar.dll on my workstation as you have, but the SP1 version on the server (naturally). Could that be why it works on my workstation but not on the server? Maybe they changed something in SP1 that resulted in a bug? Could someone try and confirm?Even more interesting: I tried running "exec xp_sqlagent_monitor 'START','',1" on the server, and after running SQL Agent was autorestarted immediately (without using the Service Control Manager) by SQLAgentMonitor. If I reboot my machine and try killing SQL Agent after it has restarted, nothing happens. If I then run xp_sqlagent_monitor again and restart SQL Agent (manually), it works.
So, again, can anyone confirm this behavior in SP1?
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
quote:
I usually setup MSSQL and SQLAGent to run under a domain account that is a member of some global group so I can assign rights. The account is a local admin, but not a domain admin.OK, I don't normally assign the domain account to the local administrators, but maybe I should.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
quote:
So, again, can anyone confirm this behavior in SP1?Sorry to be bugging you with this again, but I didn't get any reply to this (2 posts up)...
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
-
Bringing back an old one...
Just wanted to inform you guys that I finally got around to verifying this and MS has confirmed it as a bug in SP1.
Chris Hedgate @ Apptus Technologies (http://www.apptus.se)
Edited by - chrhedga on 11/13/2001 03:22:30 AM
) and I've installed it myself. No special changes made, and the only non-standard (for a SQL box) software running is IIS, but it is not really used at all. SQL Server is running as a domain account, and I even tried adding this account to the local administrators group, but this didn't change anything. I'll keep looking at it a while, then probably contact MS.
). I have the same version of xpstar.dll on my workstation as you have, but the SP1 version on the server (naturally). Could that be why it works on my workstation but not on the server? Maybe they changed something in SP1 that resulted in a bug? Could someone try and confirm?Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply