It will automatically enable Agent XPs.
(Microsoft SQL … SQL Server blocked access to procedure 'dbo.sp_get_sqlagent_properties' of component 'Agent XPs' because this component is turned off as part of the security configuration for this server.
1.
Secondly if you run SQL Agent from Services.msc it will automatically enable Agent XPs status as 1 in SP_Configure. Create a new query in SQL Server Management Studio. Execute the following: sp_configure ‘show advanced options’, 1; GO RECONFIGURE; […] Didn’t work and I got a message to enable the agent with sp_configure. RECONFIGURE; GO However, I noticed that SQL Agent was already running so I thought I would also check existing config options for 'Agent XPs' What was interesting was that config_value = 0, run_value = 1 where I was expecting config_value = 1, run_value = 1. A system administrator can enable the use of 'Agent XPs' by using sp_configure. When executed with no parameters, sp_configure returns a result set with five columns and orders the options alphabetically in ascending order, as shown in the following table.
A system administrator can enable the use of 'Agent XPs' by using sp_configure. The values for config_value and run_value are not automatically equivalent. As per Books On Line as well: Use the Agent XPs option to enable the SQL Server Agent extended stored procedures on this server. For more information about enabling 'Agent XPs', search for 'Agent XPs' in SQL Server Books Online. 2.sp_configure show advanced options , 1 and sp_config agent xps , 1.
Auf einem SQL Server 2012-Server wird Agent Xps automatisch deaktiviert.Es gibt kein festgelegtes Muster für dasselbe und das Problem begann plötzlich.Über den Profiler wurde festgestellt, dass die Anwendung SQL AGent- Enable\disable Agent Xps die XPs deaktiviert.Letzteres wird von SQL Agent-Initial BootProbe aktiviert. 2. EXEC sp_configure 'Agent XPs', 1; GO -- To update the currently configured value for this feature. If it is, start SQL Profiler, connect to the SQL Instance you are having issues with as an SysAdmin, and then Add the Exceptions and Warnings objects to the trace, start the trace and then try to start SQL Agent. When I'm trying to create a new maintenance plan in SQL Server, it is showing this error: TITLE: Microsoft SQL Server Management Studio ----- 'Agent XPs' component is turned off as part of the security configuration for this server. After installing Microsoft SQL Server 2012 I tried to start SQL Server Agent (Agent XPs disabled) in SQL Server Management Studio. sp_configure 'show advanced options', 1; GO RECONFIGURE; GO sp_configure 'Agent XPs', 1; GO RECONFIGURE GO Method 2: In this method, you do not need to write any script, you just need to open the SQL Server configuration Manager and start the SQL Server Agent as shown in the image below. 3.Now Agent Xps have changed to 1.Now i start sql agent and run sp_config.Its perfectly showing Agent xps as 1.
Look at the output of the profiler trace and see what it is reporting there.