none
Getting errors while trying to get SQL Server Agent to work RRS feed

  • Question

  • I am trying to get SQL Server Agent on an instance of 2014 and an instance of 2016 to run. Currently both of them start up but then immediately stop with the message: The SQL Server Agent (VERSION16) service on Local Computer started and then stopped. Some services stop automatically if they are not in use by other services or programs.

    I read on other issues that this is typically due to a permission issue. I checked the event viewer but all I saw was Event ID 101: SQLServerAgent service successfully started. followed by Event ID 102: SQLServerAgent service successfully stopped.

    In order to rule out a permissions issue I went into SQL Server Configuration Manager running as an admin, went to the agent properties and switched Log on to the Built-in account: Local System. When I click OK I am getting the following error: The process terminated unexpectedly. [0x8007042b].

    I am unsure where to go from here. I need this agent up as there are several nightly jobs that need to be run on a production database running on this instance. One of these is a nightly backup that is currently never executing. I am logged into the system as a user that is both a local and domain admin so there shouldn't be any permission issue here, please let me know if you can think of anything else I can try to get this working.


    Thursday, August 31, 2017 4:42 PM

Answers

  • Ended up changing the account in Microsoft Services instead of Configuration Manager to the domain admin which is also a SQL sysadmin. This has fixed the issue.
    Thursday, August 31, 2017 7:50 PM

All replies

  • Are you getting any info from the SQL Server Agent log ?

    https://docs.microsoft.com/en-us/sql/ssms/agent/view-sql-server-agent-error-log-sql-server-management-studio


    Regards,

    Javier Villegas

    DBA // MCTS // MCP

    Blog: http://sql-javier-villegas.blogspot.com.ar/  Twitter: https://twitter.com/javier_vill  /  LinkedIn: https://www.linkedin.com/in/javiervillegas/ 

    Note: Posts are provided "AS IS" without warranty of any kind, either expressed or implied, including but not limited to the implied warranties of merchantability and/or fitness for a particular purpose.

    Thursday, August 31, 2017 6:42 PM
  • The instructions that link provides to read the log requires the ability to access the Agent through management studio. Management studio is showing the SQL Server Agent (Agent XPs disabled) and as such I cannot navigate to the log folder. Is there another method to read the logs as I have been unable to find it through Google.
    Thursday, August 31, 2017 6:58 PM
  • It's just a text file. Navigate to it (e.g., C:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\MSSQL\Log\SQLAGENT.OUT) and open it with any text viewer like notepad. Open the most current one. 
    Thursday, August 31, 2017 7:10 PM
  • 2017-08-31 15:25:10 - ? [100] Microsoft SQLServerAgent version 13.0.4001.0 (X64 unicode retail build) : Process ID 19680
    2017-08-31 15:25:10 - ? [495] The SQL Server Agent startup service account is NE-REVAL\NEREVAL$.
    2017-08-31 15:25:11 - ? [393] Waiting for SQL Server to recover database 'msdb'...
    2017-08-31 15:25:11 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
    2017-08-31 15:25:11 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
    2017-08-31 15:25:11 - ! [298] SQLServer Error: 229, The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (DisableAgentXPs)
    2017-08-31 15:25:11 - ! [000] The EXECUTE permission was denied on the object 'sp_sqlagent_update_agent_xps', database 'msdb', schema 'dbo'. [SQLSTATE 42000] (Error 229)
    2017-08-31 15:25:11 - ? [098] SQLServerAgent terminated (normally)
    So it appears to be a permissions issue. I have tried adding the user NE-REVAL\NEREVAL$ with the sysadmin role, that did not fix it. When I attempt to change what user is running the agent in SQL Server Configuration Manager I get the following error: The process terminated unexpectedly. [0x8007042b]. I am running the Configuration Manager as an admin from an account that is both a Local and Domain admin so it should not be a permissions issue.
    Thursday, August 31, 2017 7:28 PM
  • Ended up changing the account in Microsoft Services instead of Configuration Manager to the domain admin which is also a SQL sysadmin. This has fixed the issue.
    Thursday, August 31, 2017 7:50 PM
  • You should definitely not use an administrative account, let alone a domain admin account, as the service account for your SQL Server Agent.

    Consider this: any user with sysadmin on your SQL box can now fairly easily perform administrative tasks on the entire domain, including creating and dropping administrative users, adding/removing computers from the domain, etc.

    Tuesday, March 13, 2018 9:46 AM
  • Daniel, you said "You should definitely not use an administrative account, let alone a domain admin account, as the service account for your SQL Server Agent."  I'm not very good with accounts and permissions as I am the sole user of my computer so it's all DDutch to me!  So what account should I use and how/where do I create it because I'm not getting anywhere with activating SQL Server Agent!  And if I use a different account for SQLSA than my general SQL work does it mean I have to log out and back in again when ever I want SQLSA to do something?

    Will be very grateful if you can guide me thanks.  

    David

    Saturday, January 18, 2020 6:34 PM
  • Normally,  you would use NT Service\SQLServerAgent and this is also the default when you install SQL Server.

    I agree with dhmacher that you should not use a domain admin account as the service account, but if this is your private laptop which is not a domain that warning is not even applicable to you.

    If you have problems with getting Agent to work, I recommend that you start a new thread and don't piggyback on an old thread. (But first check that you are not running Express Edition - Express does not support Agent.)


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Saturday, January 18, 2020 7:44 PM