none
Can't start SQL Server Agent service?

    Question

  • SQL Server Agent won't start up. 

    Agent XPs is disabled too, even though I followed the following steps

    --sp_configure 'show advanced options', 1;

    --GO

    --RECONFIGURE;

    --GO

    --sp_configure 'Agent XPs', 1;

    --GO

    --RECONFIGURE

    --GO

     

     

    I got the stack trace from below from SQL Management Studio when trying to start SQL Server Agent Service.

    ===================================

    Unable to start service SQLSERVERAGENT on server *****. (mscorlib)

    ------------------------------
    Program Location:


    Server stack trace:
       at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()
       at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
       at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)

    Exception rethrown at [0]:
       at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
       at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
       at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ServiceActionHandler.EndInvoke(IAsyncResult result)
       at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ServiceControlProgress.ActionCompletedHandler(IAsyncResult result)

    ===================================

    The SQLSERVERAGENT service on **** started and then stopped. (ObjectExplorer)

    ------------------------------
    Program Location:

       at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.Service.Start()

    Tuesday, July 12, 2011 3:07 PM

Answers

  • Check the location of the 'SQLAGENT.OUT' in case you have moved location of error logs during system database migration

    Run the below command n see if it works

    use msdb

    GO

    sp_set_sqlagent_properties @errorlog_file='New location of errorlogs\SQLAGENT.OUT'

    GO

     

    Note:you can check the location of error logs in the startup parameters in advanced tab of SQL services.

     

     

    • Marked as answer by Peja Tao Thursday, February 16, 2012 12:53 AM
    Thursday, December 08, 2011 8:33 PM

All replies

  • Hello,

    Which version and edition of SQL Server are you using?

    If it's the SQL Server 2008 Express Edition, then the agent service is installed, but because it's not included as a feature for Express Edition, you can' start it.

    Otherwise: Please have a look at the agent log and Windows event log to get more details about the problem.

     


    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Tuesday, July 12, 2011 3:20 PM
    Moderator
  • I am using SQL Server 2008 R2 Standard Edition.  Thanks.
    Tuesday, July 12, 2011 3:27 PM
  • Fine, and what about the log's? You can find the SQL Agent log in
    %ProgramFiles5\Microsoft SQL Server\<InstanceName>\MSSQL\Log => SQLAgent.out
    Olaf Helper
    * cogito ergo sum * errare humanum est * quote erat demonstrandum *
    Wenn ich denke, ist das ein Fehler und das beweise ich täglich
    Blog Xing
    Tuesday, July 12, 2011 3:45 PM
    Moderator
  • Doesn't seem quite complete, but this is the content of my SQLAgent.out file which seems to be last updated yesterday even though I have been working on it today.

     

    2011-07-11 14:02:57 - ? [131] SQLSERVERAGENT service stopping due to a stop request from a user, process, or the OS...

    2011-07-11 14:02:57 - ? [100] Microsoft SQLServerAgent version 10.50.1600.1 ((Unknown) unicode retail build) : Process ID 4392

    2011-07-11 14:02:57 - ? [101] SQL Server DEV-GREG version 10.50.1600 (0 connection limit)

    2011-07-11 14:02:57 - ? [102] SQL Server ODBC driver version 10.50.1600

    2011-07-11 14:02:57 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is

    2011-07-11 14:02:57 - ? [310] 2 processor(s) and 3840 MB RAM detected

    2011-07-11 14:02:57 - ? [339] Local computer is **** running Windows NT 6.1 (7601) Service Pack 1

    2011-07-11 14:02:58 - ? [432] There are 0 subsystems in the subsystems cache

    2011-07-11 14:02:58 - ! [000] Failed to load any subsystems. Check errorlog for details.

    2011-07-11 14:02:59 - ? [098] SQLServerAgent terminated (normally)

    Tuesday, July 12, 2011 4:14 PM
  • What about the SQL Server Error Log details?

    As per:

    2011-07-11 14:02:58 - ! [000] Failed to load any subsystems. Check errorlog for details.


    John Sansom | SQL Server DBA Blog | Twitter
    Tuesday, July 12, 2011 4:19 PM
  • This should be the corresponding error log since they are around the same time.

     

    2011-07-11 14:02:45.97 Server Microsoft SQL Server 2008 R2 (RTM) - 10.50.1600.1 (X64)

    Apr 2 2010 15:48:46

    Copyright (c) Microsoft Corporation

    Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (VM)

    2011-07-11 14:02:45.97 Server (c) Microsoft Corporation.

    2011-07-11 14:02:45.97 Server All rights reserved.

    2011-07-11 14:02:45.97 Server Server process ID is 4256.

    2011-07-11 14:02:45.97 Server System Manufacturer: 'VMware, Inc.', System Model: 'VMware Virtual Platform'.

    2011-07-11 14:02:45.97 Server Authentication mode is MIXED.

    2011-07-11 14:02:45.98 Server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG'.

    2011-07-11 14:02:45.99 Server This instance of SQL Server last reported using a process ID of 1744 at 7/11/2011 2:02:40 PM (local) 7/11/2011 6:02:40 PM (UTC). This is an informational message only; no user action is required.

    2011-07-11 14:02:45.99 Server Registry startup parameters:

    -d C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf

    -e C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Log\ERRORLOG

    -l C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\mastlog.ldf

    2011-07-11 14:02:46.02 Server SQL Server is starting at normal priority base (=7). This is an informational message only. No user action is required.

    2011-07-11 14:02:46.02 Server Detected 2 CPUs. This is an informational message; no user action is required.

    2011-07-11 14:02:46.68 Server Using dynamic lock allocation. Initial allocation of 2500 Lock blocks and 5000 Lock Owner blocks per node. This is an informational message only. No user action is required.

    2011-07-11 14:02:47.28 Server Node configuration: node 0: CPU mask: 0x0000000000000003:0 Active CPU mask: 0x0000000000000003:0. This message provides a description of the NUMA configuration for this computer. This is an informational message only. No user action is required.

    2011-07-11 14:02:48.27 spid7s Starting up database 'master'.

    2011-07-11 14:02:48.92 spid7s Recovery is writing a checkpoint in database 'master' (1). This is an informational message only. No user action is required.

    2011-07-11 14:02:49.49 spid7s FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

    2011-07-11 14:02:49.89 spid7s SQL Trace ID 1 was started by login "sa".

    2011-07-11 14:02:49.92 spid7s Starting up database 'mssqlsystemresource'.

    2011-07-11 14:02:49.94 spid7s The resource database build version is 10.50.1600. This is an informational message only. No user action is required.

    2011-07-11 14:02:50.69 spid10s Starting up database 'model'.

    2011-07-11 14:02:50.76 spid7s Server name is 'DEV-GREG'. This is an informational message only. No user action is required.

    2011-07-11 14:02:51.25 spid10s Clearing tempdb database.

    2011-07-11 14:02:52.37 Server A self-generated certificate was successfully loaded for encryption.

    2011-07-11 14:02:52.62 Server Server is listening on [ 'any' <ipv6> 1433].

    2011-07-11 14:02:52.64 Server Server is listening on [ 'any' <ipv4> 1433].

    2011-07-11 14:02:52.67 Server Server local connection provider is ready to accept connection on [ \\.\pipe\SQLLocal\MSSQLSERVER ].

    2011-07-11 14:02:52.68 Server Server local connection provider is ready to accept connection on [ \\.\pipe\sql\query ].

    2011-07-11 14:02:52.70 Server Server is listening on [ ::1 <ipv6> 1434].

    2011-07-11 14:02:52.70 spid10s Starting up database 'tempdb'.

    2011-07-11 14:02:52.83 Server Server is listening on [ 127.0.0.1 <ipv4> 1434].

    2011-07-11 14:02:52.84 Logon Error: 17187, Severity: 16, State: 1.

    2011-07-11 14:02:52.84 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: 10.161.77.46]

    2011-07-11 14:02:52.85 Server Dedicated admin connection support was established for listening locally on port 1434.

    2011-07-11 14:02:52.89 Logon Error: 17187, Severity: 16, State: 1.

    2011-07-11 14:02:52.89 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]

    2011-07-11 14:02:52.89 Logon Error: 17187, Severity: 16, State: 1.

    2011-07-11 14:02:52.89 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: ::1]

    2011-07-11 14:02:52.91 Logon Error: 17187, Severity: 16, State: 1.

    2011-07-11 14:02:52.91 Logon SQL Server is not ready to accept new client connections. Wait a few minutes before trying again. If you have access to the error log, look for the informational message that indicates that SQL Server is ready before trying to connect again. [CLIENT: <local machine>]

    2011-07-11 14:02:53.03 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/DEV-GREG.acumensolutions.com ] for the SQL Server service.

    2011-07-11 14:02:53.04 Server The SQL Server Network Interface library successfully registered the Service Principal Name (SPN) [ MSSQLSvc/DEV-GREG.acumensolutions.com:1433 ] for the SQL Server service.

    2011-07-11 14:02:53.07 Server SQL Server is now ready for client connections. This is an informational message; no user action is required.

    2011-07-11 14:02:53.07 spid13s A new instance of the full-text filter daemon host process has been successfully started.

    2011-07-11 14:02:53.23 spid15s The Service Broker protocol transport is disabled or not configured.

    2011-07-11 14:02:53.25 spid15s The Database Mirroring protocol transport is disabled or not configured.

    2011-07-11 14:02:53.39 spid15s Service Broker manager has started.

    2011-07-11 14:02:53.80 spid14s Starting up database 'msdb'.

    2011-07-11 14:02:53.81 spid20s Starting up database 'ReportServerTempDB'.

    2011-07-11 14:02:53.81 spid13s Starting up database 'ReportServer'.

    2011-07-11 14:02:54.43 spid7s Recovery is complete. This is an informational message only. No user action is required.

    2011-07-11 14:02:56.05 spid51 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

    2011-07-11 14:02:56.05 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

    2011-07-11 14:02:56.06 spid51 Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.

    2011-07-11 14:02:56.06 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

    2011-07-11 14:02:56.07 spid51 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

    2011-07-11 14:02:56.07 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

    2011-07-11 14:02:57.72 spid51 Attempting to load library 'xpsqlbot.dll' into memory. This is an informational message only. No user action is required.

    2011-07-11 14:02:57.74 spid51 Using 'xpsqlbot.dll' version '2009.100.1600' to execute extended stored procedure 'xp_qv'. This is an informational message only; no user action is required.

    2011-07-11 14:02:58.26 spid51 Attempting to load library 'xpstar.dll' into memory. This is an informational message only. No user action is required.

    2011-07-11 14:02:58.29 spid51 Using 'xpstar.dll' version '2009.100.1600' to execute extended stored procedure 'xp_instance_regread'. This is an informational message only; no user action is required.

    2011-07-11 14:02:58.58 spid51 Attempting to load library 'xplog70.dll' into memory. This is an informational message only. No user action is required.

    2011-07-11 14:02:58.63 spid51 Using 'xplog70.dll' version '2009.100.1600' to execute extended stored procedure 'xp_msver'. This is an informational message only; no user action is required.

    2011-07-11 14:02:58.89 spid51 Configuration option 'show advanced options' changed from 0 to 1. Run the RECONFIGURE statement to install.

    2011-07-11 14:02:58.89 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

    2011-07-11 14:02:58.90 spid51 Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.

    2011-07-11 14:02:58.90 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

    2011-07-11 14:02:58.91 spid51 Configuration option 'show advanced options' changed from 1 to 0. Run the RECONFIGURE statement to install.

    2011-07-11 14:02:58.91 spid51 FILESTREAM: effective level = 0, configured level = 0, file system access share name = 'MSSQLSERVER'.

    2011-07-11 14:03:02.89 spid15s Service Broker manager has shut down.

    2011-07-11 14:03:04.83 spid7s SQL Server is terminating in response to a 'stop' request from Service Control Manager. This is an informational message only. No user action is required.

    2011-07-11 14:03:04.83 spid7s SQL Trace was stopped due to server shutdown. Trace ID = '1'. This is an informational message only; no user action is required.

    2011-07-11 14:03:04.86 Server The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/DEV-GREG.acumensolutions.com ] for the SQL Server service.

    2011-07-11 14:03:04.86 Server The SQL Server Network Interface library successfully deregistered the Service Principal Name (SPN) [ MSSQLSvc/DEV-GREG.acumensolutions.com:1433 ] for the SQL Server service.

     

     
    Tuesday, July 12, 2011 4:29 PM
  • Hi

    I am also experiencing the same issues with SQL Server 2008 R2. It only occured after applying Cumulative Update #7 (it was previously RTM).  

    The error reported is the same as reported by 'mchin4acu', SQLagent.OUT is the same (but has not been written to since applying CU7), Error log has the same output.

    Any thoughts would be greatly appreciated!

    thanks

    Rod

    Wednesday, July 13, 2011 12:14 AM
  • Does the below query list any subsystems as explained here?

     

    SELECT * FROM msdb..syssubsystems
    





    Pradeep Adiga
    Blog: sqldbadiaries.com
    Twitter: @PradeepAdiga

    Wednesday, July 13, 2011 10:20 AM
  • Hi

    I am also experiencing the same issues with SQL Server 2008 R2. It only occured after applying Cumulative Update #7 (it was previously RTM).  

    The error reported is the same as reported by 'mchin4acu', SQLagent.OUT is the same (but has not been written to since applying CU7), Error log has the same output.

    Any thoughts would be greatly appreciated!

    thanks

    Rod


    Friend, Could you please check your summary.log which is normally at location C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap. Had seen the same issue after upgrade where incomplete installation was found as a problem..
    Harsh Chawla Personal Blog:- http://blogs.msdn.com/batala Team Blog:- http://blogs.msdn.com/b/sqlserverfaq/
    Wednesday, July 13, 2011 3:07 PM
  • Hi Harsh and Pradeep

    the summary log did seem to indicate a successful patch operation.

    Except from Summary.log:

    Overall summary:
      Final result:                  Passed
      Exit code (Decimal):           0
      Exit message:                  Passed
      Start time:                    2011-07-13 05:20:27
      End time:                      2011-07-13 05:27:08
      Requested action:              Patch

    with no negatives in the rest of the log.

    As Pradeep suggested, I had a look at the msdb..syssubsystems and found that all DLL present and in correct location, but 5 Agent exe files are totally missing from C:\Program Files\Microsoft SQL Server\100\COM?! (only 22 dll files in this folder)

    4 of the missing files are from CU7, and 1 untouched by CU. I am starting to think the issue may have arisen from a SQL server repair run on the server prior to applying the patch. This repair was run as the patch would not install as it detected a previous install/repair had not completed.

    I will need to take the server offline tonight to do any further modifications, unless you can suggest anything better, I will run a repair install from original media,  and then rerun the Cumulative Patch.

    I will post results tomorrow.

    Many thanks

    Rod

    Thursday, July 14, 2011 5:37 AM
  • Hi Rod,

    Could you please let us know the results you got?


    Best Regards,
    Peja

    Please remember to click "Mark as Answer" on the post that helps you, and to click "Unmark as Answer" if a marked post does not actually answer your question. This can be beneficial to other community members reading the thread.
    Tuesday, July 26, 2011 8:46 AM
  • I would love to know how this worked out because I have the same issue.
    Thursday, November 17, 2011 6:44 PM
  • Check the location of the 'SQLAGENT.OUT' in case you have moved location of error logs during system database migration

    Run the below command n see if it works

    use msdb

    GO

    sp_set_sqlagent_properties @errorlog_file='New location of errorlogs\SQLAGENT.OUT'

    GO

     

    Note:you can check the location of error logs in the startup parameters in advanced tab of SQL services.

     

     

    • Marked as answer by Peja Tao Thursday, February 16, 2012 12:53 AM
    Thursday, December 08, 2011 8:33 PM
  • The last thread about sp_set_sqlagent_properties nailed it for me!  Thanks Aamir!!!!!!!!!!!!!!!!!!!!!!!
    Wednesday, February 15, 2012 2:34 PM
  • Hello there,

    I have tried pretty much everything and still the agent doesnt come up. I have tried the command above and i m getting this error Any clues?

    Msg 15281, Level 16, State 1, Procedure sp_set_sqlagent_properties, Line 0

    SQL Server blocked access to procedure 'dbo.sp_set_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online.

    Thursday, February 23, 2012 4:14 PM
  • Run this command to enable Agent XP

    sp_configure 'show advanced options', 1;
    GO
    RECONFIGURE;
    GO
    sp_configure 'Agent XPs', 1;
    GO
    RECONFIGURE
    GO

    Monday, February 27, 2012 6:19 PM
  • If all else fails here, you might want to check if the services is just turned off:

    Go to Start->Run (type services.msc)

    Browse through the Colleciton, and look for SQL Server Agent and make sure its Status is "STARTED" and its startup type is "AUTOMATIC", though being Automatic is just secondary to your question.

    HTH

    Monday, March 25, 2013 8:27 AM