July 16, 2008 at 10:01 am
I recently applied the latest security patch KB948108 to my test environment. It completed with no errors. After the upgrade I entered Management Studio but the SQL Server Agent is red and says the agent XPs are disabled. When I go into configuration manager it says SQL Server Agent is started and when I look at Windows Services, it says it is started as well. I tried to use the sp_configure command as well and it still will not start correctly. Anyone else have this problem or know how to fix it? I am using SQL Server 2005 SP2 (9.00.3175). After the upgrade I am now on version 9.00.3233. At this point I will not be putting this into production anytime soon.
July 16, 2008 at 11:44 pm
[font="Verdana"]Restart your sql agent service from configuration manager. If it failed to started, post back sqlagent.out file content.[/font]
Regards..Vidhya Sagar
SQL-Articles
July 17, 2008 at 8:03 am
I was able to restart the sql agent from the configuration manager with no problems. However when I restarted the sql server service, I get the same problem. When I look at the services it says sql agent is started, but inside sql server management studio it says it's disabled. The sqlagent.out file has the following information:
2008-07-17 09:33:24 - ? [393] Waiting for SQL Server to recover databases...
2008-07-17 09:40:12 - ? [100] Microsoft SQLServerAgent version 9.00.3233.00 (x86 unicode retail build) : Process ID 1296
2008-07-17 09:40:12 - ? [101] SQL Server XXX-DEV version 9.00.3233 (0 connection limit)
2008-07-17 09:40:12 - ? [102] SQL Server ODBC driver version 9.00.3042
2008-07-17 09:40:12 - ? [103] NetLib being used by driver is DBNETLIB.DLL; Local host server is
2008-07-17 09:40:12 - ? [310] 2 processor(s) and 1024 MB RAM detected
2008-07-17 09:40:12 - ? [339] Local computer is XXX-DEV running Windows NT 5.2 (3790) Service Pack 2
2008-07-17 09:40:12 - ? [432] There are 11 subsystems in the subsystems cache
2008-07-17 09:40:13 - ! [364] The Messenger service has not been started - NetSend notifications will not be sent
2008-07-17 09:40:13 - ? [129] SQLSERVERAGENT starting under Windows NT service control
2008-07-17 09:40:13 - + [260] Unable to start mail session (reason: No mail profile defined)
2008-07-17 09:40:13 - + [396] An idle CPU condition has not been defined - OnIdle job schedules will have no effect
Thanks for your help.
July 17, 2008 at 8:12 am
One other thing I noticed in the Event Log was the following message:
Configuration option 'Agent XPs' changed from 1 to 0. Run the RECONFIGURE statement to install.
This occurred after I restarted the SQL Server service and it automatically restarts the SQL Agent service.
Then I manually re-start the SQL Agent and the Event log has the following message:
Configuration option 'Agent XPs' changed from 0 to 1. Run the RECONFIGURE statement to install.
Why would it change it from 1 to 0 in the first place?
October 6, 2008 at 3:43 pm
Same problem, different scenario.
(overall goal, installing Team Foundation Server 2008)
1) installed SQL Server 2005 w/SP1
2) installed Windows Sharepoint Service 3.0 (unbeknownst to me, this install redistributed something called "SQL Server Embedded Edition")
3) After further recommendation, installed SP2 for the aforementioned SQL Server 2005 installation.
4) attempted to install TFS, got error message stating that SQL Server Agent services are not running
I am an admin and I have attempted to start SQL Server Agent services via: Administrative Tools, Configuration Manager, and SQL Server Surface Area Configuration. None of these are working for me.
SQL Server Surface Area Configuration gives me a generic message about needing to be an admin to start....I am an admin.
Help???
October 7, 2008 at 3:58 am
I think the agent XPs setting may be your real problem.
try running this via SSMS to permanatly set this on:
sp_configure 'show advanced options', 1
GO
RECONFIGURE
GO
sp_configure 'Agent XPs', 1
GO
RECONFIGURE
GO
---------------------------------------------------------------------
October 7, 2008 at 8:22 am
Hi,
Check if this article applies to you...
http://support.microsoft.com/kb/943525/en-us
"You cannot start the SQL Server Agent service of a failover cluster of SQL Server 2005 if the build of SQL Server is 3179 or a later build".
Regards,
Jose Oyervides.
November 17, 2008 at 1:53 am
Hello. I got the same problem. If I set Agent XPs to enable and try to start the service the system sets Agent XPs back to disabled. I have never seen this behavior before. If anyone have a solution I be gratefull. I run both SQL and Agent services under Local System account on a singel machine.
Regard Joel
November 17, 2008 at 6:45 am
Thanks Joel. Glad I'm not the only one with this problem. I haven't continued to test thought since I am rebuilding this test environment. Let me know if you find a solution.
November 22, 2008 at 5:06 pm
And just to make sure, you don't feel lonely, I've got the same issue since aprox 13.11.08.... And browsing to search for solutions. I wouldn't be surprised htat for some reason some security settings are no longer working for us.... lets hope somebody has a solution
Johann
November 23, 2008 at 4:22 am
is the sp_configure option AWE enabled set on for you? (= 1). The one time I had problems with agents xp option it was because awe was enabled but the lock pages in memory option was not set. After granting this right and restarting SQL I was able to set agents XP and it stayed set.
God knows why this would have an effect but it did the trick.
One other thing I would check is if running sqlagent under a windows account this has been explicitly added as a login and granted sysadmin.
This was on a SQL 2005 SP2 + GDR2 box. (9.00.2054)
---------------------------------------------------------------------
December 11, 2008 at 10:06 am
Add me to the list of baffled and hassled.
Best I can do is run backups manually. Forget nightly scripts that aren't vital.
In my case, someone allowed someone else to upgrade my 2000 install, tho I had specifically laid out my plan for the upgrade to happen Jan 09 and I am The DBA here.
Imagine my surprise when I go to put that server into production and find 2005 install?
Clearly there were errors with the upgrade, which of course were not communicated to me either. Trolling the logs has been horrendous. But this box is in production, I cannot go backwards.
It looks like in our situation that the msdb did not get all its tables. I'm planning to steal them from a "good" 2005 server but am not optimistic. That install also errored but at least the Agent functions.
I'm so glad it's not Just Me! Being a non-profit, this is the first steps into 05 and I have not had a test server in 2 years so absolutely NO experience with it. Against my advice, this initiative has been rushed into without careful consideration.
:crying:
December 11, 2008 at 1:39 pm
AWE only applies to Enterprise, doesn't it?
December 12, 2008 at 9:13 am
December 12, 2008 at 10:38 am
Ok, that's what I thought, thanks. I recall something about AWE from 2000 that didn't apply to us.
I scripted msdb system tables from a working 2005 server and now have msdb tables on my, um, limping server. running the sqlagent90 with the switches has changed things, pointing me to the next problem:
stored procedures seem to be missing. Sure enuf, there are 75 vs over 1800 on my "good server."
I have not found where I can script those. I selected All Objects, but there were no sp's created there, just roles, defaults and schemas.
I'm gonna hit the books for a while, hoping I can have the agent back before the weekend.
edited to add: I think that perhaps "my good server" is not good at all, since scripting All Objects from "the bad one" did at least include table creates for the existing msdb objects. crap.
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply