Solved

sql server agent xps disabled automatically

Posted on 2014-04-10
10
1,097 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
  • 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 42

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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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 42

Assisted Solution

by:Eugene Z
Eugene Z earned 167 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 167 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 166 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

730 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