?
Solved

sql server agent xps disabled automatically

Posted on 2014-04-10
10
Medium Priority
?
1,255 Views
Last Modified: 2015-04-05
A few days ago at 4:30am, somehow SQL Server Agent XPS (extended stored procedures) was automatically disabled. This occurred on a SQL Server 2005 Enterprise Edition system. There was only a single job scheduled around that time that ran at 4:30, which was a DBCC CHECKDB on all databases. That job actually succeeded and the duration was normal.

The error logs showed: Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install. When trying to open a maintenance plan, the error showed: '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).

The following code fixed the issue:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;

sp_configure

GO
sp_configure 'Agent XPs', 1;
GO
RECONFIGURE
GO

I just don't know the cause! Has anyone experienced this? Either way, does anyone know the cause?

Thanks,

pae2
0
Comment
Question by:pae2
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +3
10 Comments
 
LVL 20

Expert Comment

by:Marten Rune
ID: 39995332
The change did not do anything, quote:
"The error logs showed: Configuration option 'user options' changed from 0 to 0. Run the RECONFIGURE statement to install"
This shows it Was 0, and reconfigured to 0, You are probably looking at the wrong Place. Something else made your job fail.

Regards Marten
0
 
LVL 12

Expert Comment

by:Carlo-Giuliani
ID: 39996282
Are you sure Agent XPS was enabled before?

This is disabled by default as a security measure.   The message is a bit misleading...it does not indicate that the setting is being changed at that moment.
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 39996815
please make sure you have latest sql 2005 service pack
select @@version
How to obtain the latest service pack for SQL Server 2005
http://support.microsoft.com/kb/913089
----

and  after you have set 'Agent XPs' to 1  (check running value)
sp_configure 'Agent XPs'
you will be ok

note:  sql 2005 sometimes likes more :
SQL Server Configuration Manager to start SQL Server services
0
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

Author Comment

by:pae2
ID: 39996844
Marten/Carlo-Giuliani, I did not look at the wrong place. I am 100% sure of that. There were four databases running logs every 10-minutes up until 4:30 when somehow SQL Server Agent XPS was disabled. It is odd how the log stated that it went from 0 to 0. But the agent was unquestionably running before 4:30. I still don't know how the XPS value changed. If you have anymore theories, please share them.

Thanks,

pae2
0
 

Author Comment

by:pae2
ID: 39996845
EugeneZ I have the latest service-pack on the machine. That was a good suggestion, but that's not it. I can't check the running value at this time because I'd have to go through a formal process to actually make that change. I might be able to do that next week. However, after I ran the statements above, everything has been executing properly. If you have anymore theories, please share them.

Thanks,

pae2
0
 
LVL 43

Assisted Solution

by:Eugene Z
Eugene Z earned 501 total points
ID: 39997378
go via sql error log and check when \what changed this value.
if you see nothing: set audit and try to get this info later.
0
 
LVL 69

Accepted Solution

by:
Scott Pletcher earned 501 total points
ID: 40002305
Check for previous messages on all the event logs, including SQL Server, SQL Server Agent and maybe even the application and Windows log if nothing else shows up.

If someone accidentally stopped the Agent service, that would disable the XPs, but they should re-enable themselves automatically when the Agent service starts back up.  Therefore, it seems some must have explicitly set it to off if it was off.  I know SQL will disable jobs on its own some times, but I don't know of any situations where it disables Agent altogether on its own.
0
 
LVL 35

Expert Comment

by:David Todd
ID: 40002338
or if the SQL service was stopped, stopping the agent as well, but only the SQL service was restarted ...
0
 
LVL 20

Assisted Solution

by:Marten Rune
Marten Rune earned 498 total points
ID: 40011713
This has to be a manual mistake, not the one you're looking at where configuration changed from 0 to 0. This does nothing, that's for sure.

regards Marten
0
 

Author Comment

by:pae2
ID: 40013806
Apologies, I will get back to this. I had other production priorities. I will aim to get to this tomorrow during biz hours. pae2
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question