September 27, 2001 at 2:32 pm
All properties in SQL 2000 & agent have been set to auto restart if SQL 2000 is shutdown. SQL Server agent is not restarting. It can be manually started. Have numerous jobs assigned to 2 different servers and need to have this auto restart. Both have Win2k and are recently upgraded databases from 6.5. Any ideas would be appreciated.
September 27, 2001 at 6:56 pm
Sometimes the SQL Agent doesn't restart because it checks the status of the MSSQLServer service and it may not have started when it makes its attempt to start.
Try going to the services (via the control panel/Administrative Tools) and set the SQLServerAgent service recovery options with the following:
First failure: restart service after 2 minutes.
You can repeat for the second and third attempts.
Also find out what may be delaying the startup of the MSSQLService.
October 5, 2001 at 10:43 am
Tried setting up the service as described, but it is still not working. Will research why it's delaying maybe I'll find more information that way. Thanks.
October 5, 2001 at 2:10 pm
In EM, the properties for SQLAgnet include a "restart SQL Agent" if stoppped. Try that.
Steve Jones
October 8, 2001 at 6:50 pm
That is already checked as well, both under the properties for the SQL Agent and SQL Server.
November 30, 2001 at 1:04 pm
Does anybody have other thoughts on this one. The auto restart works fine if the server itself is rebooted. But if SQLServer is stopped & restarted, the SQLServerAgent does not start back up.
November 30, 2001 at 1:18 pm
I noticed this too. I think this is an MS bug with the system. It seems that it will only restart SQLAgnet if then service fails for some reason, not if SQL is restarted.
Steve Jones
November 30, 2001 at 3:15 pm
Thanks for your help. That's kinda what I was afraid of. I'll just keep a mental note to restart if the SQL server is taken down. Hopefully SP2 is coming out soon.
November 30, 2001 at 5:41 pm
I wouldnt expect it to change in sp2. The problem becomes, how would SQL "know" that starting the agent was the right thing to do? If you had just stopped it to make a change or whatever and wanted it to start, sure. But what if you had it disabled on purpose...so that jobs didnt run? That's just my guess. I'll agree its a pain!
Andy
December 21, 2001 at 10:09 am
If you stop a service (e.g. MSSQLServer), all dependent services (here SQLServerAgent) are stopped also.
When you start a service no dependent services are started.
You can avoid this by using the "Restart" option in the Services Applet (W2K only).
The feature to automatically restart the SQLServerAgent is executed by the MSSQLServer service und is executed only if SQLServerAgent stopped UNEXPECTEDLY
(which is not the case if you stop MSSLserver intentionally and SQLServerAgent is stopped also in this process).
I myself have a batch Script scheduled to run before Maintenance Operations
(e.g. before nightly backups) in the at (Schedule) Service which runs QSVC.EXE
to inquire service state and NETSVC.EXE to start the service if necessary.
You need QSVC.EXE, SLEEP.EXE and NETSVC.EXE from ResKit in %windir%\Commands to run the batch:
@echo OFF
set $$Server=%COMPUTERNAME%
set $$Service=SQLServerAgent
set $$Recipients=YourOperatorEmailAddress
if NOT "%1x" == "x" set $$Server=%1
if NOT "%2x" == "x" set $$Service=%2
if NOT "%3x" == "x" set $$Recipients=%3
if "%1x" == "x" set $$Server=%COMPUTERNAME%
%WinDir%\Commands\QSVC.EXE \\%$$Server% %$$Service%
if ERRORLEVEL 11 goto unknown
if ERRORLEVEL 10 goto error
if ERRORLEVEL 7 goto paused
if ERRORLEVEL 6 goto pausedpending
if ERRORLEVEL 5 goto continuepending
if ERRORLEVEL 4 goto alive
if ERRORLEVEL 3 goto stoppending
if ERRORLEVEL 2 goto startpending
if ERRORLEVEL 1 goto stopped
if ERRORLEVEL 0 goto nostatus
%WinDir%\Commands\QSVC.EXE \\%$$Server% %$$Service%
isql -S%$$Server% -E -Q"EXEC master.dbo.sp__servicstate '%$$Recipients%',%ERRORLEVEL%"
goto exit
:alive
rem isql -S%$$Server% -E -Q"EXEC master.dbo.sp__servicealive '%$$Recipients%'"
goto exit
:dead
:unknown
:error
:paused
:pausedpending
:continuepending
:stoppending
:stopped
:startpending
:nostatus
isql -S%$$Server% -E -Q"EXEC master.dbo.sp__servicedead '%$$Recipients%'"
%WinDir%\Commands\NETSVC.EXE %$$Service% \\%$$Server% /continue
%WinDir%\Commands\SLEEP.EXE 10
%WinDir%\Commands\NETSVC.EXE %$$Service% \\%$$Server% /start
%WinDir%\Commands\SLEEP.EXE 10
%WinDir%\Commands\QSVC.EXE \\%$$Server% %$$Service%
rem isql -S%$$Server% -E -Q"EXEC master.dbo.sp__servicestate '%$$Recipients%',%ERRORLEVEL%"
goto exit
:exit
set $$Recipients=
set $$Service=
set $$Server=
@echo ON
December 21, 2001 at 10:41 am
December 28, 2001 at 1:24 pm
I have seen this before and it could be due to mail client weirdness. And those errors do show up in the SQL Agent log. Take a look...
January 22, 2002 at 11:28 pm
There is a bug introduced in SQL2000 SP1 described in KB article Q302892.
If you have an Operator in SQL Agent that uses more than one email account, any notification for an alert or job can cause the SQLSERVERAGENT service to stop.
Workaround is to use email group on your exchange server or install SP2.
It is very difficult to reproduce.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply