[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

sql server agent xps disabled automatically

Posted on 2014-04-10
10
Medium Priority
?
1,458 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 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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

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 70

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

834 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