Solved

sql server agent xps disabled automatically

Posted on 2014-04-10
10
996 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:EugeneZ
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
 

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
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 42

Assisted Solution

by:EugeneZ
EugeneZ 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:
ScottPletcher 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Suggested Solutions

How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
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…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
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…

867 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now