?
Solved

sql 2008 server agent stops

Posted on 2011-10-26
12
Medium Priority
?
1,007 Views
Last Modified: 2012-05-12
The server agent stops after a couple of hours or days. Really can't find anything specific in the event viewer. I have repeatedly start the service but it always turns off then my backups won't run. Here are a few entries:

Log Name:      Application
Source:        MSSQLSERVER
Date:          10/13/2011 3:23:25 AM
Event ID:      26037
Task Category: Server
Level:         Information
Keywords:      Classic
User:          N/A
Computer:      Localdrivingserver
Description:
The SQL Server Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.

Log Name:      Application
Source:        MSSQLSERVER
Date:          9/12/2011 2:16:23 PM
Event ID:      18204
Task Category: Server
Level:         Error
Keywords:      Classic
User:          NETWORK SERVICE
Computer:      Localdrivingserver
Description:
BackupDiskFile::CreateMedia: Backup device 'C:\Program Files\Goldmine\gmbackup\local_daily.bak' failed to create. Operating system error 3(The system cannot find the path specified.).
0
Comment
Question by:goldmineexpert
  • 6
  • 6
12 Comments
 

Author Comment

by:goldmineexpert
ID: 37032595
The server agent will start when I manually start it. I can run my backups manually with no issues but I go back and look at the agent and it's stopped again.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37032621
You should check the box in SSMS that tell the Agent to automatically restart if it stops unexpectedly.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37032627
But to get to the core problem, look in the Agent error log.  There should be something in there if it's stopping for no apparent reason.
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37032643
>> You should check the box in SSMS that tell the Agent to automatically restart if it stops unexpectedly. <<

That is, from SSMS, right-click on "SQL Server Agent", select "Properties", then check the box "Auto restart SQL Server Agent..."
0
 

Author Comment

by:goldmineexpert
ID: 37032644
sql agent log file:


2011-09-28 14:26:59 - ? [100] Microsoft SQLServerAgent version 10.0.1600.22 ((Unknown) unicode retail build) : Process ID 5832
2011-09-28 14:26:59 - ? [101] SQL Server LOCALDRIVINGSER version 10.00.1600 (0 connection limit)
2011-09-28 14:26:59 - ? [102] SQL Server ODBC driver version 10.00.1600
2011-09-28 14:26:59 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2011-09-28 14:26:59 - ? [310] 8 processor(s) and 8182 MB RAM detected
2011-09-28 14:26:59 - ? [339] Local computer is LOCALDRIVINGSER running Windows NT 6.0 (6002) Service Pack 2
2011-09-28 14:27:00 - ? [432] There are 7 subsystems in the subsystems cache
2011-09-28 14:27:00 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2011-09-28 14:27:00 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2011-09-28 14:27:01 - + [260] Unable to start mail session (reason: No mail profile defined)
2011-09-28 14:27:01 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
2011-10-13 03:20:32 - ? [130] SQLSERVERAGENT stopping because of Windows shutdown...
2011-10-13 03:20:34 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2011-10-13 03:20:34 - ! [382] Logon to server '(local)' failed (SaveAllSchedules)
2011-10-13 03:20:35 - ! [165] ODBC Error: 0, Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
2011-10-13 03:20:35 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)
2011-10-13 03:20:36 - ? [098] SQLServerAgent terminated (normally)
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37032660
Great, thanks.

And there's your answer:
"
SQLSERVERAGENT stopping because of Windows shutdown...
"

Change the SQL Server Agent service to automatically start at Windows start up -- it must be set to manual now.
0
 

Author Comment

by:goldmineexpert
ID: 37032686
0
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 37032728
A Windows restart is different.  That is controlled by the Windows service.

You can use the SQL Configuration tool to set the service to be Automatic start instead of Manual start after Windows boots up.
0
 

Author Comment

by:goldmineexpert
ID: 37032763
Error Logs from the Server Agent  10/13/2011

Date,Source,Severity,Message
10/13/2011 03:20:36,,Information,[098] SQLServerAgent terminated (normally)
10/13/2011 03:20:35,,Error,[382] Logon to server '(local)' failed (DisableAgentXPs)
10/13/2011 03:20:35,,Error,[165] ODBC Error: 0<c/> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
10/13/2011 03:20:34,,Error,[382] Logon to server '(local)' failed (SaveAllSchedules)
10/13/2011 03:20:34,,Error,[165] ODBC Error: 0<c/> Driver's SQLAllocHandle on SQL_HANDLE_ENV failed [SQLSTATE IM004]
10/13/2011 03:20:32,,Information,[130] SQLSERVERAGENT stopping because of Windows shutdown...
09/28/2011 14:27:01,,Warning,[396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
09/28/2011 14:27:01,,Warning,[260] Unable to start mail session (reason: No mail profile defined)
09/28/2011 14:27:00,,Information,[129] SQLSERVERAGENT starting under Windows NT service control
09/28/2011 14:27:00,,Error,[364] The Messenger service has not been started - NetSend notifications will not be sent
09/28/2011 14:27:00,,Information,[432] There are 7 subsystems in the subsystems cache
09/28/2011 14:26:59,,Information,[339] Local computer is LOCALDRIVINGSER running Windows NT 6.0 (6002) Service Pack 2
09/28/2011 14:26:59,,Information,[310] 8 processor(s) and 8182 MB RAM detected
09/28/2011 14:26:59,,Information,[103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
09/28/2011 14:26:59,,Information,[102] SQL Server ODBC driver version 10.00.1600
09/28/2011 14:26:59,,Information,[101] SQL Server LOCALDRIVINGSER version 10.00.1600 (0 connection limit)
09/28/2011 14:26:59,,Information,[100] Microsoft SQLServerAgent version 10.0.1600.22 ((Unknown) unicode retail build) : Process ID 5832
0
 

Author Comment

by:goldmineexpert
ID: 37032791
I found the the auto start option. It was set to manual.  I will watch it over the next few days and let you know what I find.

Thanks, bob
0
 

Author Closing Comment

by:goldmineexpert
ID: 37032793
thanks for you help.  Will keep you updated.
0
 
LVL 70

Expert Comment

by:Scott Pletcher
ID: 37032860
Great, thanks.

Sorry didn't provide more immediately on the Automatic start, but I'm pretty busy, have to find time for quick break to post here between actual work tasks :-) .
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Integration Management Part 2

569 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question