March 13, 2018 at 9:18 am
I have a very strange situation on SQL Server that I cannot fathom out.
Environment : SQL Server 2012 SP3 CU3 running on a 2 node Windows 2008 R2 cluster
In SQL Server Management Studio\Management\Maintenance Plans\ I am unable to create or edit existing plans.
I receive the error:
'Agent XPs' component is turned off as part of the security configuration for this server. A system administrator can enable the use of 'Agent XPs' by using sp_configure. For more information about enabling 'Agent XPs', see "Surface Area Configuration" in SQL Server Books Online. (ObjectExplorer)
Checking around that error I expected the following config was going to be required.
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'Agent XPs', 1;
GO
-- To update the currently configured value for this feature.
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.
I thought I'd try the sp_configure solution to 'force' the config but when I ran it (step by step), the first RECONFIGURE statement just hung and indeed when it ran I could not even run an sp_who2 to see if it was blocking or being blocked.
The only way I could kill the RECONFIGURE was to close the query window which cancelled it. I therefore am unable to run EXEC sp_configure 'Agent XPs', 1 as the required RECONFIGURE cannot be run.
After a failover of the cluster, the config settings for 'Agent XPs' remains at config_value = 0, run_value = 1.
Has anyone got any ideas as to how to fix it?
March 13, 2018 at 11:23 am
Don't know for sure, but you may need to configure that on both instances, or possibly during down time for a given node. Worst case might be "undoing" the cluster long enough to configure both instances and then re-doing the cluster, but I just don't have the detailed knowledge to know...
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 13, 2018 at 12:30 pm
Thanks for feedback Steve
Eventually I found a clue here...
So I recreated the problem
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
In a new query run
select * FROM sys.dm_exec_requests where blocking_session_id > 0
--returns that spid 86 is blocking a CONFIGURE process (from the RECONFIGURE tsql statement) which needs a
--LCK_M_SCH_M lock on METADATA: database_id = 1 SERVER_PRINCIPAL(principal_id = 1)
Cancel the RECONFIGURE query
In a new query window
EXEC sp_whoisactive
--SPID 86 running
--SELECT type, data FROM sys.fn_MSxe_read_event_stream (@source, @sourceopt)
--LoggedInAs : SYSTEM\extendedeventlogger
--Program : XEventSqlStreamProvider Event Session: extended_event_logger
So the use of XEventSqlStreamProvider (which I assume is an Extended Events analyzer) must be taking a lock on the master db (database_id = 1) that is preventing the LCK_M_SCH_M lock that the RECONFIGURE command needs.
Its end of day for me now so I'm not going to be messing around with production server tonight, but pretty sure if I stop the service that is running the XEventSqlStreamProvider I'll then be able to config Agent XPs to get rid of the weird config_value = 0, run_value = 1 setting and retry the maintenance plan editor.
-- To enable the feature.
EXEC sp_configure 'Agent XPs', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
But that's for a new day!
Report back tomorrow.
March 13, 2018 at 12:36 pm
Cool beans. I'll look forward to seeing your results.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 14, 2018 at 8:26 am
Hi Steve
Found that the extended event logger was running as a clustered service so took this approach.
One bit of weirdness was that after step 1 the running state of config Agent XPs changed from config_value = 0, run_value = 1 to config_value = 0, run_value = 0
After steps 2 and 3 the config state of Agent XPs changed to config_value = 1, run_value = 1 which was what I was looking for. After step 4 those config values remained in place.
In the end I have set the extended event logger service on the db cluster to offline with autostart disabled as it appears that it was only set up to investigate a problem that occurred way back in 2015 (way before my time here) and it shouldn't be running BAU.
Perry
March 14, 2018 at 9:30 am
Perry,
Thanks for the suggested edit on dba.se but we cannot accept answers in the body of a question.
I'm not sure it makes sense to reopen the old question either, but I might be wrong about that.
If you have time, we would certainly appreciate a separate self-answered Q & A though.
Cheers.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
March 15, 2018 at 7:09 am
Perry Dyball - Wednesday, March 14, 2018 8:26 AMHi SteveFound that the extended event logger was running as a clustered service so took this approach.
- Take the extended event logger clustered service offline
- EXEC sp_configure 'Agent XPs', 1;
- RECONFIGURE;
- Put extended event logger clustered service back online
- Retested the maintenance plan editor and everything worked
One bit of weirdness was that after step 1 the running state of config Agent XPs changed from config_value = 0, run_value = 1 to config_value = 0, run_value = 0
After steps 2 and 3 the config state of Agent XPs changed to config_value = 1, run_value = 1 which was what I was looking for. After step 4 those config values remained in place.
In the end I have set the extended event logger service on the db cluster to offline with autostart disabled as it appears that it was only set up to investigate a problem that occurred way back in 2015 (way before my time here) and it shouldn't be running BAU.
Perry
Excellent result! Plus you get the benefit of no longer wasting cycles on something not actually needed.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply