none
SQL SERVER AGENT ISSUE

    Question

  • Im not able to  re enable the SQL SERVER AGENT SERVICE

    I followed the microsoft online  instructions

    I tried the script that uses sp_configure
    but  I could never get the enabled the SERVER AGENT  again

    Finally I made a backup of all data bases and removed the instance and reinsaled it, Im certain that this is the worst solution to by pass the problem, I you experts have any better solution then I will be gratefull


    The complexity resides in the simplicity
    Tuesday, July 27, 2010 4:13 PM

All replies

  • Please post additional details in this forum

    1) SQL Server version

    2) SQL Server Edition

    3) Detailed repro scenario

    4) Check SQLAgent.out log in MSSQL\Log folder

    5) Check Windows event log for any error messages reported from SQl Agent

    6) Check SQl Server error log in MSSQL\Logs

     

    Thanks

    Sethu Srinivasan[MSFT]

    SQL Server Agent Team

    http://blogs.msdn.com/sethus

     

    Tuesday, July 27, 2010 5:52 PM
    Moderator
  • Do you have permisson to start the service?
    Manish
    Tuesday, October 04, 2011 8:28 AM
  • Hi,

    The account that start the servicepolicies must be enabled :

     "act as part operating system", and "logon as a service",  or  if You prefer local administrator

    You tried start the services with local account?, Any errors?, pelase view the event viewer. And send us 

     

    regards,

     

    Carlos Augusto.

     

     


    If the answer was helpful, please mark it as useful. Si la respuesta le ayudó, por favor márquela como útil.
    • Proposed as answer by LearnerSql Tuesday, October 04, 2011 3:46 PM
    Tuesday, October 04, 2011 2:44 PM
  • I resolved the issue with below steps:

    Check Event log or more specifically SQL Agent Log.

    My system was named instance so i goto named instance name protocol TCPIP anf found the dynamic port.

    Checked sql agent properties in SSMS and Connection propery.

    Sql and Windows both were disabled. while alias was enabled.

    i created the alias with port above and mentioned that port to alias server name in Sql agent property and after that i started the sql agent.

    it started gracefully.

     sqg agent property Errorlog:

    C:\Program Files\Microsoft SQL Server\MSSQL10.VISHNU\MSSQL\Log\SQLAGENT.OUT

    2011-10-04 22:55:05 - ? [100] Microsoft SQLServerAgent version 10.0.1600.22 ((Unknown) unicode retail build) : Process ID 6344
    2011-10-04 22:55:05 - ? [101] SQL Server BHAVYAA version 10.00.1600 (0 connection limit)
    2011-10-04 22:55:05 - ? [102] SQL Server ODBC driver version 10.00.1600
    2011-10-04 22:55:05 - ? [103] NetLib being used by driver is DBMSSOCN.DLL; Local host server is SqlAgent
    2011-10-04 22:55:05 - ? [310] 4 processor(s) and 4044 MB RAM detected
    2011-10-04 22:55:05 - ? [339] Local computer is BHAVYAA running Windows NT 6.1 (7601) Service Pack 1
    2011-10-04 22:55:05 - ! [000] SQLServerAgent cannot start because the instance of the server (MSSQLSERVER) is not the expected instance (VISHNU)
    2011-10-04 22:55:06 - ? [098] SQLServerAgent terminated (normally)
    and

    2011-10-04 22:52:46 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]
    2011-10-04 22:52:46 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
    2011-10-04 22:52:46 - ! [298] SQLServer Error: 65535, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
    2011-10-04 22:52:46 - ! [000] Unable to connect to server 'BHAVYAA\VISHNU'; SQLServerAgent cannot start
    2011-10-04 22:52:51 - ! [298] SQLServer Error: 65535, SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. [SQLSTATE 08001]
    2011-10-04 22:52:51 - ! [165] ODBC Error: 0, Login timeout expired [SQLSTATE HYT00]
    2011-10-04 22:52:51 - ! [298] SQLServer Error: 65535, A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online. [SQLSTATE 08001]
    2011-10-04 22:52:51 - ! [382] Logon to server 'BHAVYAA\VISHNU' failed (DisableAgentXPs)
    2011-10-04 22:52:51 - ? [098] SQLServerAgent terminated (normally)

     Another Solution :

    EXEC msdb.dbo.sp_set_sqlagent_properties @local_host_server = NULL; --It will set sql server name for sql agent.

    • Edited by LearnerSql Tuesday, October 04, 2011 6:21 PM
    Tuesday, October 04, 2011 5:15 PM
  • Ah!, fortunatelly  You could solve

     

    Carlos Augusto.


    If the answer was helpful, please mark it as useful. Si la respuesta le ayudó, por favor márquela como útil.
    • Proposed as answer by LearnerSql Saturday, October 08, 2011 4:16 AM
    Wednesday, October 05, 2011 1:43 AM
  • I had to start the SQL Browser to resolve this issue. Usually I turn off the SQL server browser and use the DB with IP only. To resolve the issue I tried what has been suggested above but they did not work for me. like Sethu Srinivasan[MSFT] suggested I reviewed the agent log file and noticed that the instance is being referenced with server\instance name and I tried adding to the network connection alias, adding sql agent connection property server information that didn't work. So I started the SQL browser and the issue is gone. But that is not what I wanted I want to keep the sql browser turned off. So I still need to find a way to make SQL agent work with IP address and port number instead of using the instnace name and server name.
    Tuesday, September 13, 2016 5:07 PM
  • I had to start the SQL Browser to resolve this issue. Usually I turn off the SQL server browser and use the DB with IP only. To resolve the issue I tried what has been suggested above but they did not work for me. like Sethu Srinivasan[MSFT] suggested I reviewed the agent log file and noticed that the instance is being referenced with server\instance name and I tried adding to the network connection alias, adding sql agent connection property server information that didn't work. So I started the SQL browser and the issue is gone. But that is not what I wanted I want to keep the sql browser turned off. So I still need to find a way to make SQL agent work with IP address and port number instead of using the instnace name and server name.

    I appreciate your effort on this,but make a note its an old thread.

    if you have any other issues related to sql server - please open an new thread.


    Regards, S_NO "_"

    Wednesday, September 14, 2016 1:08 AM