Agent XPs automagically being disabled
-
Has anyone ever seen this before? I think I may have discovered a new bug!
Here's what I have:
- SQL Server 2005 Standard Edition 9.00.2153 (SP1)
- Windows Server 2003 Enterprise Edition SP1
Here's what's happening:
- In SSMS, SQL Server Agent appears in the Object Browser with "Agent XPs disabled" next to it
- I run sp_configure to change 'Agent XPs' to 1. This doesn't need a restart
- I try to start SQL Server Agent (whether in SSMS or in the Services applet)
- I get a message saying the service has started and stopped
- When I run sp_configure, I see that 'Agent XPs' has changed back to 0
- If I use Profiler to see what is going on, I notice that it's the attempted start of SQL Server Agent that sets 'Agent XPs' back to 0
- The ApplicationName column in Profiler for this activity shows the application as being "SQLAgent - Enabling/disabling Agent XPs"
- The SQL that it traps is as follows:
declare
@agent_enabled bit
declare @show_advanced bit
select @show_advanced = cast(value_in_use as bit)
from sys.configurations where name = N'show advanced options'
select @agent_enabled = cast(value_in_use as bit)
from sys.configurations where name = N'Agent XPs'
if 0 <> @agent_enabled
begin
if 1 <> @show_advanced
begin
exec sys.sp_configure @configname = N'show advanced options', @configvalue = 1
reconfigure with override
end
exec sys.sp_configure @configname = N'Agent XPs', @configvalue = 0
reconfigure with override
if 1 <> @show_advanced
begin
exec sys.sp_configure @configname = N'show advanced options', @configvalue = 0
reconfigure with override
end
end
- I have restarted the SQL Server service but the problem is still there
- I have searched but can't find any information on this
If anyone knows anything about this, or failing that, how to report this problem to Microsoft, I'd be grateful.
Thanks
John
-
Hi John,
I'm having the following,SQL 2005 Evaluation edition and Windows XP professional SP2.I tried the following as u mentioned,
1.Stopped sql agent and found that Agent Xps were in disabled state.
2.sp_configure show advanced options , 1 and sp_config agent xps , 1.
3.Now Agent Xps have changed to 1.Now i start sql agent and run sp_config.Its perfectly showing Agent xps as 1.
So I dont find any problem with XP Sp2 and SQL 2005 evaluation edition.May be with SQL 2005 SP1 it mite occur not sure.
Cheers
Deepak
[font="Verdana"]- Deepak[/font]
-
Have you checked the error logs to see if the there is a problem while SQL Server Agent is starting? The only time I have seen Agent XP's disabled is when the service is stopped.
-
The one thought I have is, did someone create and enable a policy on the machine that enforces a rule that the Agent be stopped? A policy would initiate a process to stop the Agent.
-
Thanks for your help, everybody. I must admit that I didn't realise that the Agent XPs are automatically disabled when SQL Server Agent stops. So they were a bit of a red herring - the issue I have is that SQL Server Agent starts and then immediately stops again. There is nothing in the SQL error log (save that the service account connected to the database), there is no SQLAGENT.OUT produced, and the Windows event log reports only that the service started, and then that it stopped, both events occurring at exactly the same time.
Paul - I'm not sure where I would find a policy such as you mention. I've looked in Local Security Policy and can't find anything. I've also asked one of our Windows admins and he said doesn't know of any way of configuring anything in Windows to produce this behaviour.
This was working at some point... the last SQLAGENT.OUT I have is from 15th May.
If anybody has any further ideas, I'd be pleased to hear them.
Thanks
John
-
There are 2 undocumented permissions that SQL Server 2005 needs in order to run correctly. Most people do not hit these, but our service accounts are NOT Local Administrators and we have access permissions set on most services via GPOs.
SQL Server needs Read access to the Remote Procedure Call (RPC) service in order to initialise the MSDTC interface.
SQL Agent needs Read access to the Event Log service in order to start.
All other service permissions are given in BOL: Setting up Windows Service Accounts. AWE requirements are given in BOL: Enabling memory support for over 4GB of Physical Memory. Instant File Initialization requirements are given in BOL: Database File Initialization
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Ed
Thanks. How do I grant Read access to the event log service? I'm still baffled, because I've changed the service to run under my own domain login (which is a local admin) and it still doesn't work.
John
-
You can use Security Configuration Manager (a W2003 tool) to build the existing permissions. The default is that everyone can read the Event Log service details but if your site protects services via GPOs this could have changed.
If the service is GPO protected you will have to talk to whoever manages GPOs to get the GPO changed. The wonderful thing about the Security section of GPOs is that it will force the machine configuration back to what is in the GPO at every refresh cycle (default 90 minutes).
There may be something in the Windows Event Logs about why Agent did not start. Have you checked the 3 logs - System, Application, Security.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Another thought - have you confirmed that the Agent account has read access to the SQL Server program libraries, and that the Agent account is a known login to SQL Server. Both of these issues would result in Agent stopping as soon as it starts.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Ed
Thanks for this. I've tried everything now - it doesn't work either under the local system account, or my own domain account, which is a local admin. There's nothing in the event log - it just tells me that the service was successfully sent a start control, and then immediately that it entered the stopped state. Think I might have to reinstall.
Cheers
John
-
I am having the exact same problem, started recently, could it have to do with sp2 recent fixes?
-
Are you saying you think SP2 has broken it, or that you think it might be fixed by SP2? I couldn't find SQL Server Agent mentioned in the SP2 bug fix list, at least not in any way that related to this problem. I haven't installed SP2 yet.
John
-
Seems like the problem started, after applying a fix to sp2,
not really sure
-
Our standard build includes SP2 and hotfix rollup 3152. Agent runs fine here.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
-
Don't be so quick to give up. Just need to dig a little deeper, because you could re-install and the problem still exist. I would suggest using regmon, filemon, or processmon from sysinternals (now MS) to see if the process if failing to access some needed reg keys, or files. Hopefully this will point you in the right direction. If I remember correct SQLagent is like Perfmon service, even if it is set to start automatically if there is nothing for it to do it will stop. For example if you have no SQLagent jobs it will probably stop right after starting.
Viewing 15 posts - 1 through 15 (of 35 total)
You must be logged in to reply to this topic. Login to reply