In one of SQL Server 2012 server, agent Xps is getting disabled automatically. There is no set pattern for the same and the issue started all of a sudden. Through profiler it was found that application named SQL AGent- Enable\disable Agent Xps is disabling the XPs. And the later is enabled by SQL Agent-Initial BootProbe.
Sql-server – Agent Xps getting disabled automatically
sql serversql-server-2012sql-server-agent
Related Solutions
It is whenever the agent service is stopped that the run_value of sp_configure 'Agent XPs'changes to 0. Whenever the agent service is started, it is set automatically back to 1.
This is expected behaviour. When you STOP sql server agent, the congiguration option Agent XPs changes the config_value and run_value to 0. When you start sql agent, then the config_value and run_value for Agent XPs changes back to 1.
It is important to run reconfigure with override when you enable the configuration option for the first time using sp_configure 'Agent XPs', 1
Since you recently upgraded a few months ago from SQL Server 2005 EE to 2012 SE, you should save off and clear your event logs because SqlAgent may crash when it sees Event Log IDs from an older build of SQL Server. For example, the Agent may crash because it reads an event logged by a SQL Server build earlier than the current build, which only contains data with ONE insertion string and the agent will try to format it using the latest resource information that requires TWO strings. This would lead to a brief memory leak just before it shuts down since a buffer is allocated for the string that it is trying to print into when the crash occurs.
As far as the agent service not listed in configuration manager, there are multiple possibilities: https://support.microsoft.com/en-us/kb/941823
Ensure you are running 2012 Configuration Manager
You can try rebuilding the WMI Repository: https://blogs.technet.microsoft.com/askperf/2009/04/13/wmi-rebuilding-the-wmi-repository/
Best Answer
I had a similar problem that I just resolved. Turned out, a new group policy had been applied to our server. This group policy disallowed domain users access to the D:, E:, & F: drives. Our SQL Server Agent was configured to write out its error log to the E: drive. As the Log On account for the SQL Server Agent service was a domain based account, SQL Server Agent was now unable to write to its log file. It died immediately at service start up. It was very frustrating in that we saw nothing in the Windows Event Viewer other than that the service started up and then stopped.