asked on

sql 2008 server agent stops

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.).

goldmineexpert

ASKER

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.

Scott Pletcher

You should check the box in SSMS that tell the Agent to automatically restart if it stops unexpectedly.

Scott Pletcher

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.

Scott Pletcher

>> 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..."

goldmineexpert

ASKER

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)

Scott Pletcher

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.

goldmineexpert

ASKER

see file
10-26-2011-10-41-08-AM.jpg

ASKER CERTIFIED SOLUTION

Scott Pletcher

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

goldmineexpert

ASKER

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

goldmineexpert

ASKER

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

goldmineexpert

ASKER

thanks for you help. Will keep you updated.

Scott Pletcher

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 :-) .