• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1825
  • Last Modified:

sql server agent xps disabled automatically

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
pae2
Asked:
pae2
  • 3
  • 2
  • 2
  • +3
3 Solutions
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
Carlo-GiulianiCommented:
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
 
Eugene ZCommented:
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
pae2Author Commented:
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
 
pae2Author Commented:
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
 
Eugene ZCommented:
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
 
Scott PletcherSenior DBACommented:
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
 
David ToddSenior DBACommented:
or if the SQL service was stopped, stopping the agent as well, but only the SQL service was restarted ...
0
 
Marten RuneSQL Expert/Infrastructure ArchitectCommented:
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
 
pae2Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

  • 3
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now