none
SQL Server Agent won't start

    Question

  • Hi everyone,

    Host: Server 2012 R2

    SQL version: 2014 Std

    I just updated the SQL Server Agent to "log on" using a domain Administrator account as SQL backups were failing to a network share.

    Now I can't get the SQL Server Agent service to start at all!

    SQL Server Agent started then stopped

    After looking at the log file C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Log\SQLAGENT.out, I tried the solution at https://www.mssqltips.com/sqlservertip/2729/how-to-start-sql-server-agent-when-agent-xps-show-disabled/ but the service still won't start.

    Can someone please help?

    SQLAGENT.out log:

    2017-04-06 11:37:49 - ? [100] Microsoft SQLServerAgent version 12.0.5000.0 (X64 unicode retail build) : Process ID 13084
    2017-04-06 11:37:49 - ? [495] The SQL Server Agent startup service account is DOMAIN\Administrator.
    2017-04-06 11:37:49 - ? [393] Waiting for SQL Server to recover database 'msdb'...
    2017-04-06 11:37:49 - ? [000] Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
    2017-04-06 11:37:49 - ? [101] SQL Server SERVER01 version 12.00.5000 (0 connection limit)
    2017-04-06 11:37:49 - ? [102] SQL Server ODBC driver version 11.00.5643
    2017-04-06 11:37:49 - ? [103] NetLib being used by driver is DBNETLIB; Local host server is
    2017-04-06 11:37:49 - ? [310] 8 processor(s) and 24576 MB RAM detected
    2017-04-06 11:37:49 - ? [339] Local computer is SERVER01 running Windows NT 6.2 (9200)
    2017-04-06 11:37:49 - ! [298] SQLServer Error: 22022, Usage:  EXECUTE xp_sqlagent_notify 'A', NULL, NULL, <alert_id>, <action type> [SQLSTATE 42000]
    2017-04-06 11:37:49 - ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
    2017-04-06 11:37:49 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
    2017-04-06 11:37:49 - ? [098] SQLServerAgent terminated (normally)

    Thursday, April 6, 2017 1:47 AM

Answers


  • 2017-04-06 11:37:49 - ! [298] SQLServer Error: 22022, Usage:  EXECUTE xp_sqlagent_notify 'A', NULL, NULL, <alert_id>, <action type> [SQLSTATE 42000]
    2017-04-06 11:37:49 - ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
    2017-04-06 11:37:49 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
    2017-04-06 11:37:49 - ? [098] SQLServerAgent terminated (normally)

    Hi,

    I guess above is the error which is stopping agent from coming online. Please refer to below link

    https://mssqlwiki.com/tag/sql-agent-failure/


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by marleybytes Tuesday, April 11, 2017 10:37 PM
    Thursday, April 6, 2017 4:45 AM
    Moderator

All replies

  • 2017-04-06 11:37:49 - ? [393] Waiting for SQL Server to recover database 'msdb'..

    Something wrong goes with msdb database, I hope you have a good last backup. Also take a look into Event Viewer to see more info about the error.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Thursday, April 6, 2017 3:45 AM

  • 2017-04-06 11:37:49 - ! [298] SQLServer Error: 22022, Usage:  EXECUTE xp_sqlagent_notify 'A', NULL, NULL, <alert_id>, <action type> [SQLSTATE 42000]
    2017-04-06 11:37:49 - ! [442] ConnConnectAndSetCryptoForXpstar failed (0).
    2017-04-06 11:37:49 - ? [000] Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install. [SQLSTATE 01000] (Message 15457)
    2017-04-06 11:37:49 - ? [098] SQLServerAgent terminated (normally)

    Hi,

    I guess above is the error which is stopping agent from coming online. Please refer to below link

    https://mssqlwiki.com/tag/sql-agent-failure/


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    • Marked as answer by marleybytes Tuesday, April 11, 2017 10:37 PM
    Thursday, April 6, 2017 4:45 AM
    Moderator
  • Setting the hostname then restarting the server resolved the issue:

    HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.X\SQLServerAgent
    Modify the value data of the serverhost key to np:Virtualservername

    Tuesday, April 11, 2017 10:48 PM