SQL Agent

Question

SQL Agent

sudhakara

SSCrazy Eights

Points: 9425
More actions
March 27, 2010 at 5:49 am

#139563

Hi..
i have found the following error message in the sql server agent.
SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs)
Pls share your knowledge how to resolve and root cause for this.
SQL server Details:
Sql server 2005 Standard Edition
Service pack 1
32 bit
Thanks in Advance.

Viewing 15 posts - 1 through 15 (of 22 total)

You must be logged in to reply to this topic. Login to reply

vmssanthosh SSC Eights! Points: 856 More actions · Answer 1

hi

did you change the server role by any chance. Check whether you are able to access msdb database with the login specified to run the job at the agent

Regards

VMSSanthosh

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 2

vmssanthosh (3/27/2010)
hi
did you change the server role by any chance. Check whether you are able to access msdb database with the login specified to run the job at the agent
Regards
VMSSanthosh

Yes,i can able to access the msdb there is no issues on that.i have log into the server by user name SA.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 3

Any suggestion will be appreciated.

Thanks in Advance.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 4

Did you have SP1 patch recently?

Whats values you are getting with it?

select value, value_in_use from sys.configurations where name = 'Agent XPs'

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 5

Additionally Are you taking backup of MSDB ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 6

Bhuvnesh (3/29/2010)
Did you have SP1 patch recently?
Whats values you are getting with it?
select value, value_in_use from sys.configurations where name = 'Agent XPs'

Yes. i have applied service pack 1 and results of the above query

Value Value in use

1 1

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 7

Bhuvnesh (3/29/2010)
Additionally Are you taking backup of MSDB ?

No. i have not taken the backup of MSDB.

JKG SSCommitted Points: 1563 More actions · Answer 8

May seem like a stupid question, but is anything actually not working, or are you just getting the error in the logs? Looking around, this seems to be a bug in SP1 which can mean database mail stops working, although I read something else that suggests it's a bogus error (ie just an annoyance).

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 9

yes it happened because of SP1 i think MS has fixed this bug iin SP2.

refer these links

http://www.windows-tech.info/15/4b5bdaab8e755fbe.php

http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic15824.aspx

http://dbaspot.com/forums/sqlserver-programming/350238-sql2005-sql-agent-error.html

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 10

Bhuvnesh (3/30/2010)
yes it happened because of SP1 i think MS has fixed this bug iin SP2.
refer these links
http://www.windows-tech.info/15/4b5bdaab8e755fbe.php
http://www.sqlnewsgroups.net/group/microsoft.public.sqlserver.server/topic15824.aspx
http://dbaspot.com/forums/sqlserver-programming/350238-sql2005-sql-agent-error.html

Thanks for your reply.

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 11

JKG (3/29/2010)
May seem like a stupid question, but is anything actually not working, or are you just getting the error in the logs? Looking around, this seems to be a bug in SP1 which can mean database mail stops working, although I read something else that suggests it's a bogus error (ie just an annoyance).

Sql agent job is not running which has been created only through maintanance plan.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 12

sudhakara (3/30/2010)
Thanks for your reply.

Did it get resolve ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

sudhakara SSCrazy Eights Points: 9425 More actions · Answer 13

Bhuvnesh (3/30/2010)
sudhakara (3/30/2010)
Thanks for your reply.
Did it get resolve ?

Let you know once i have applied the service pack 3 on the sql server.

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 14

SP3 or SP2 ?

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)