SQL agent not coming up after upgrade

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SQL Server Administration (2005)

SQL agent not coming up after upgrade

Author

Topic

kumarich1
Yak Posting Veteran

99 Posts

Posted - 2009-03-17 : 09:36:42

Hi,

After doing inplace upgrade. Sql server is running fine all databases are fine in new version except sql agent . SQL agent is at change pending state and it is not coming up . I have tried rebooting many times. Please advise.

Thanks

mfemenel
Professor Frink

1421 Posts

Posted - 2009-03-17 : 10:19:01

Make sure the sql agent account has access to all of the sql agent xp's.

Mike
"oh, that monkey is going to pay"

kumarich1
Yak Posting Veteran

99 Posts

Posted - 2009-03-17 : 11:04:03

I reboot server ,then sql server agent started running. When I tried to run a job or open properties of a job. I am getting below error. This is all happening after upgrade. Please advise

Msg 14258, Level 16, State 1, Procedure sp_is_sqlagent_starting, Line 8
Cannot perform this operation while SQLServerAgent is starting. Try again later.

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-03-17 : 12:18:59

sp_configure 'show advanced option',1
GO
--NOW CHECK AGENT XPs
SP_CONFIGURE
--I'm sure AGENT XPs setting is disabled. Enable it and your problem will go away
SP_CONFIGURE 'AGENT XPs',1
GO
RECONFIGURE

Moral of the story: Just upgrading database is not enough, you need to make post upgrade changes. This is one of 'em.

kumarich1
Yak Posting Veteran

99 Posts

Posted - 2009-03-17 : 12:47:09

quote:
Originally posted by saurabhsrivastava

sp_configure 'show advanced option',1
GO
--NOW CHECK AGENT XPs
SP_CONFIGURE
--I'm sure AGENT XPs setting is disabled. Enable it and your problem will go away
SP_CONFIGURE 'AGENT XPs',1
GO
RECONFIGURE

Moral of the story: Just upgrading database is not enough, you need to make post upgrade changes. This is one of 'em.

These are the values currently on the server, I have run the above script.Still same issue.
Name Min Max config_value run_value
Agent XPs 0 1 1 1

saurabhsrivastava
Posting Yak Master

216 Posts

Posted - 2009-03-17 : 12:52:21

did you restart service, after changing it.

kumarich1
Yak Posting Veteran

99 Posts

Posted - 2009-03-17 : 13:44:10

quote:
Originally posted by saurabhsrivastava

did you restart service, after changing it.

This is the porblem I am having since yesterday, when I stop and start sql agent service it won't stop and start in timely manner it errors out and status will be in stopping status forever, I have rebooted the whole box now, agent status is in starting status.Now I am in same situation as yesterday. In the sql server configuration manager state of the sql agent is change pending.

Please advise.

kumarich1
Yak Posting Veteran

99 Posts

Posted - 2009-03-17 : 14:01:03

When I did reboot second time , then agent came up fine and also the issue has gone. I don't know why I have to reboot two times.

Thanks for your help

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources