none
Migrated the SQL server 2014 to 2016.But the agent is not running even manually started ,after some inter-well it got stopped running RRS feed

  • Question

  • Migrated the SQL server 2014 to 2016.

    But the agent is not running even manually started ,after some inter-well it got stopped running.We have changed the running status to Automatic.

    I have changed the AgentXps to 1 ,but agent is abruptly stopped working.

    Below is the error i have.Please help me to resolve the issue.

    2019-07-15 19:28:19 - ? [100] Microsoft SQLServerAgent version 13.0.4001.0 (X64 unicode retail build) : Process ID 16064

    2019-07-15 19:28:19 - ? [495] The SQL Server Agent startup service account is PARADISE\administrator.

    2019-07-15 19:28:19 - ! [150] SQL Server does not accept the connection (error: 0). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.

    2019-07-15 19:28:49 - ! [150] SQL Server does not accept the connection (error: 0). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.

    2019-07-15 19:29:19 - ! [150] SQL Server does not accept the connection (error: 0). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.

    2019-07-15 19:29:49 - ! [150] SQL Server does not accept the connection (error: 0). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.

    2019-07-15 19:30:19 - ! [150] SQL Server does not accept the connection (error: 0). Waiting for Sql Server to allow connections. Operation attempted was: Verify Connection On Start.

    2019-07-15 19:30:19 - ! [000] Unable to connect to server '(local)'; SQLServerAgent cannot start

    2019-07-15 19:30:19 - ! [165] ODBC Error: 0, Data source name not found and no default driver specified [SQLSTATE IM002]

    2019-07-15 19:30:19 - ! [165] ODBC Error: 0, Data source name not found and no default driver specified [SQLSTATE IM002]

    2019-07-15 19:30:19 - ! [165] ODBC Error: 0, Data source name not found and no default driver specified [SQLSTATE IM002]

    2019-07-15 19:30:19 - ! [165] ODBC Error: 0, Data source name not found and no default driver specified [SQLSTATE IM002]

    2019-07-15 19:30:19 - ! [165] ODBC Error: 0, Data source name not found and no default driver specified [SQLSTATE IM002]

    2019-07-15 19:30:19 - ! [382] Logon to server '(local)' failed (DisableAgentXPs)

    2019-07-15 19:30:19 - ? [098] SQLServerAgent terminated (normally)

    Wednesday, July 17, 2019 5:41 AM

All replies

  • Unable to connect to server '(local)'; SQLServerAgent cannot start

    Is the SQL Server running? By the error message it don' seems so.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Wednesday, July 17, 2019 6:14 AM
  • Hi,

    Yes SQL server is running ,but agent is not getting started.

    If manually stated the Agent then it will  terminated.


    Wednesday, July 17, 2019 6:57 AM
  • Hi All,

    SQL Agent getting Stopped abnormally even starts manually in SQLserver 2016.

    I have enabled all agentxp's and Check the Service accounts ..But i could not able to get the Solution.

    Please help me on this.

    Wednesday, July 17, 2019 9:31 AM
  • Hi,

     

    Would you please show us the detailed message in sql server agent log(the default path:C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLAgent.1)?

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 18, 2019 5:43 AM
  • Hi,

     

    Would you please try to change the service account of sql server agent service to local system and check if there is a similar problem?

     

    >>ODBC Error: 0, Data source name not found and no default driver specified [SQLSTATE IM002]

     

    It seems the issue with ODBC driver And would you please try to apply ODBC 13.1 for your server. For more details, please refer to https://support.microsoft.com/en-nz/help/3185365/sql-server-2016-agent-fails-to-start-or-failed-to-retrieve-data-error

     

    Best regards,

    Dedmon Dai


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Thursday, July 18, 2019 7:52 AM