|
dev
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Agent keeps failingMessage [298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs) I mention that I have imported by detach - atach databases from sql 2000 to sql 2005 standard ed and made some maintenance plans (created in sql2005) to backup databases during the night and notify by mail if not. database mail is set, also with public profile and default profile checked. The service is set to restart if it fails but it is not doing so. The databases are in sql 2000 compatibility mode - planning to set them in 2005 compatibility mode soon. Also I tried to disable notifications in all maintenance plans with no luck - agent is stoping unexpectedly from time to time and it's not restarting by itself. I have also this error in job history: Message [LOG] Exception 5 caught at line 431 of file t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent initiating self-termination Please advise Thanks Adrian Ciuca. "Adrian Ciuca" <no_spam_ciucaadrian@pcnet.ro> wrote in message I'm having this same problem after installing SP1 and the current cumulative news:%23RM%23awSkGHA.3496@TK2MSFTNGP02.phx.gbl... > My sql agent keeps failin with the following error: > Message > [298] SQLServer Error: 2812, Could not find stored procedure > 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs) > .... hot fix. I also posted on microsoft.public.sqlserver.server Bryan Hi Adrian,
The error about the call to xp_sqlagent_notify is a benign startup error, and we're looking at fixing it for the next service pack. Without more information I can't tell why agent is failing, but I believe that the job history error that you're mentioning might give us a clue. [LOG] Exception 5 caught at line 431 of file t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent initiating self-termination This says it's an acces violation that is likely caused by one of the jobs that are running, so can you take a look at the jobs that you have on that machine and see if any one of them has the potential to cause an AV? -- Show quoteCiprian Gerea SDE, SqlServer This posting is provided "AS IS" with no warranties, and confers no rights. "BDB" <b**@reply.to.group.com> wrote in message news:OHR1msVkGHA.3496@TK2MSFTNGP02.phx.gbl... > > "Adrian Ciuca" <no_spam_ciucaadrian@pcnet.ro> wrote in message > news:%23RM%23awSkGHA.3496@TK2MSFTNGP02.phx.gbl... >> My sql agent keeps failin with the following error: >> Message >> [298] SQLServer Error: 2812, Could not find stored procedure >> 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs) >> > ... > > I'm having this same problem after installing SP1 and the current > cumulative hot fix. I also posted on microsoft.public.sqlserver.server > > Bryan > In case you haven't noticed it, the error refers to not finding
msdb.dbo.XP_sqlagent_notify during startup. Poking around on a CLEAN SQL 2005 server, I notice that XP_sqlagent_notify is only available in the master database, not in msdb. In msdb, a similar procedure is called SP_sqlagent_notify. Could this just be a bug in the startup code of SQL Agent that Microsoft may have overlooked? Ciprian Gerea [MSFT] wrote: Show quote > Hi Adrian, > > The error about the call to xp_sqlagent_notify is a benign startup error, > and we're looking at fixing it for the next service pack. Without more > information I can't tell why agent is failing, but I believe that the job > history error that you're mentioning might give us a clue. > > [LOG] Exception 5 caught at line 431 of file > t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent > initiating self-termination > > This says it's an acces violation that is likely caused by one of the jobs > that are running, so can you take a look at the jobs that you have on that > machine and see if any one of them has the potential to cause an AV? > > -- > Ciprian Gerea > SDE, SqlServer > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > "BDB" <b**@reply.to.group.com> wrote in message > news:OHR1msVkGHA.3496@TK2MSFTNGP02.phx.gbl... > > > > "Adrian Ciuca" <no_spam_ciucaadrian@pcnet.ro> wrote in message > > news:%23RM%23awSkGHA.3496@TK2MSFTNGP02.phx.gbl... > >> My sql agent keeps failin with the following error: > >> Message > >> [298] SQLServer Error: 2812, Could not find stored procedure > >> 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs) > >> > > ... > > > > I'm having this same problem after installing SP1 and the current > > cumulative hot fix. I also posted on microsoft.public.sqlserver.server > > > > Bryan > > Let me make it more clear - yes, we're calling this xp with the wrong prefix
during startup. It always fails, and we are going to fix this problem in the next SP. But this failure is benign - it's ignored and agent starts out fine afterwards. Adrian's problem is an access violation happening in the code that executes jobs, and I can't tell for sure what's going on there without knowing what those jobs do, so he needs to take a look and see if there's something that stands out. If for instance a job would execute an ActiveX script that calls into a COM object that generates an access violation then agent shuts down and it can output one of those errors. -- Show quoteCiprian Gerea SDE, SqlServer This posting is provided "AS IS" with no warranties, and confers no rights. "Neptune" <viju.me***@aglc.gov.ab.ca> wrote in message news:1150756472.632275.264840@h76g2000cwa.googlegroups.com... > In case you haven't noticed it, the error refers to not finding > msdb.dbo.XP_sqlagent_notify during startup. Poking around on a CLEAN > SQL 2005 server, I notice that XP_sqlagent_notify is only available in > the master database, not in msdb. In msdb, a similar procedure is > called SP_sqlagent_notify. Could this just be a bug in the startup code > of SQL Agent that Microsoft may have overlooked? > > Ciprian Gerea [MSFT] wrote: >> Hi Adrian, >> >> The error about the call to xp_sqlagent_notify is a benign startup error, >> and we're looking at fixing it for the next service pack. Without more >> information I can't tell why agent is failing, but I believe that the job >> history error that you're mentioning might give us a clue. >> >> [LOG] Exception 5 caught at line 431 of file >> t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent >> initiating self-termination >> >> This says it's an acces violation that is likely caused by one of the >> jobs >> that are running, so can you take a look at the jobs that you have on >> that >> machine and see if any one of them has the potential to cause an AV? >> >> -- >> Ciprian Gerea >> SDE, SqlServer >> >> This posting is provided "AS IS" with no warranties, and confers no >> rights. >> >> >> "BDB" <b**@reply.to.group.com> wrote in message >> news:OHR1msVkGHA.3496@TK2MSFTNGP02.phx.gbl... >> > >> > "Adrian Ciuca" <no_spam_ciucaadrian@pcnet.ro> wrote in message >> > news:%23RM%23awSkGHA.3496@TK2MSFTNGP02.phx.gbl... >> >> My sql agent keeps failin with the following error: >> >> Message >> >> [298] SQLServer Error: 2812, Could not find stored procedure >> >> 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs) >> >> >> > ... >> > >> > I'm having this same problem after installing SP1 and the current >> > cumulative hot fix. I also posted on microsoft.public.sqlserver.server >> > >> > Bryan >> > > It is a suspicious job there besides normal backup restore operations - I
disabled it for the time being to see if agent is still failing. It was written for sql2000 I think - I'll ask our programmers to take a look over it (I don't know to decipher it) I'll post back, thanks for the response. PS - indeed, the agent starts even that the xp_sqlagent_notify error occur - that confused me first time - I suspected that mail notification will cause agent to fail if that stored procedure is not found - and I disabled all notification - obvious that did't stop the unexpected behavior of the sqlagent. Adrian Show quote "Ciprian Gerea [MSFT]" <Ciprian.Ge***@online.microsoft.com> wrote in message news:ObIyrfAlGHA.3304@TK2MSFTNGP03.phx.gbl... > Let me make it more clear - yes, we're calling this xp with the wrong > prefix during startup. It always fails, and we are going to fix this > problem in the next SP. But this failure is benign - it's ignored and > agent starts out fine afterwards. > > Adrian's problem is an access violation happening in the code that > executes jobs, and I can't tell for sure what's going on there without > knowing what those jobs do, so he needs to take a look and see if there's > something that stands out. If for instance a job would execute an ActiveX > script that calls into a COM object that generates an access violation > then agent shuts down and it can output one of those errors. > > -- > Ciprian Gerea > SDE, SqlServer > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > > "Neptune" <viju.me***@aglc.gov.ab.ca> wrote in message > news:1150756472.632275.264840@h76g2000cwa.googlegroups.com... >> In case you haven't noticed it, the error refers to not finding >> msdb.dbo.XP_sqlagent_notify during startup. Poking around on a CLEAN >> SQL 2005 server, I notice that XP_sqlagent_notify is only available in >> the master database, not in msdb. In msdb, a similar procedure is >> called SP_sqlagent_notify. Could this just be a bug in the startup code >> of SQL Agent that Microsoft may have overlooked? >> >> Ciprian Gerea [MSFT] wrote: >>> Hi Adrian, >>> >>> The error about the call to xp_sqlagent_notify is a benign startup >>> error, >>> and we're looking at fixing it for the next service pack. Without more >>> information I can't tell why agent is failing, but I believe that the >>> job >>> history error that you're mentioning might give us a clue. >>> >>> [LOG] Exception 5 caught at line 431 of file >>> t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent >>> initiating self-termination >>> >>> This says it's an acces violation that is likely caused by one of the >>> jobs >>> that are running, so can you take a look at the jobs that you have on >>> that >>> machine and see if any one of them has the potential to cause an AV? >>> >>> -- >>> Ciprian Gerea >>> SDE, SqlServer >>> >>> This posting is provided "AS IS" with no warranties, and confers no >>> rights. >>> >>> >>> "BDB" <b**@reply.to.group.com> wrote in message >>> news:OHR1msVkGHA.3496@TK2MSFTNGP02.phx.gbl... >>> > >>> > "Adrian Ciuca" <no_spam_ciucaadrian@pcnet.ro> wrote in message >>> > news:%23RM%23awSkGHA.3496@TK2MSFTNGP02.phx.gbl... >>> >> My sql agent keeps failin with the following error: >>> >> Message >>> >> [298] SQLServer Error: 2812, Could not find stored procedure >>> >> 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs) >>> >> >>> > ... >>> > >>> > I'm having this same problem after installing SP1 and the current >>> > cumulative hot fix. I also posted on >>> > microsoft.public.sqlserver.server >>> > >>> > Bryan >>> > >> > > Ok, that suspicious job I was talking about is just a calling for a stored
procedure wich is called also in other programs during normal operations. It cannot cause an AV. Other jobs are just doing backup operations during the night. This time I have the following errors cronologically at one second distance one from another: 1st: Exception 5 caught at line 431 of file t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent initiating self-termination. (both windows and sql event log) 2nd: EventType sql90exception, P1 sqlagent90.exe, P2 2005.90.2047.0, P3 443f5c88, P4 sqlagent90.exe, P5 2005.90.2047.0, P6 443f5c88, P7 0, P8 0003983b, P9 00000000, P10 NIL. (windows event log) and this is as information in windows event log: Bucket 05385461, bucket table 5, EventType sql90exception, P1 sqlagent90.exe, P2 2005.90.2047.0, P3 443f5c88, P4 sqlagent90.exe, P5 2005.90.2047.0, P6 443f5c88, P7 0, P8 0003983b, P9 00000000, P10 NIL. I made some tests yesterday (including starting manually that stored procedure mentioned before and starting the job manually) with no errors. At the time when agents stops I can see no scheduled task set. Could be that this job is scheduled "when idle" and finding out idle condition raise an exception? thanks. Unfortunately you are hitting a bug that we knew about and that is going to
be fixed in SP2. Like you hinted, having jobs on CPU idle will crash Agent. There's no workaround 'till then, so you might want to consider changing the schedule to a predefined time window when you know the server is not under significant load (e.g. 2 to 4 am) if that is at all possible. -- Show quoteCiprian Gerea SDE, SqlServer This posting is provided "AS IS" with no warranties, and confers no rights. "Adrian Ciuca" <no_spam_ciucaadrian@pcnet.ro> wrote in message news:uJHR1mPlGHA.1344@TK2MSFTNGP03.phx.gbl... > Ok, that suspicious job I was talking about is just a calling for a stored > procedure wich is called also in other programs during normal operations. > It cannot cause an AV. Other jobs are just doing backup operations during > the night. > This time I have the following errors cronologically at one second > distance one from another: > 1st: > > Exception 5 caught at line 431 of file > t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent > initiating self-termination. > (both windows and sql event log) > > 2nd: > > EventType sql90exception, P1 sqlagent90.exe, P2 2005.90.2047.0, P3 > 443f5c88, P4 sqlagent90.exe, P5 2005.90.2047.0, P6 443f5c88, P7 0, P8 > 0003983b, P9 00000000, P10 NIL. > (windows event log) > > and this is as information in windows event log: > > Bucket 05385461, bucket table 5, EventType sql90exception, P1 > sqlagent90.exe, P2 2005.90.2047.0, P3 443f5c88, P4 sqlagent90.exe, P5 > 2005.90.2047.0, P6 443f5c88, P7 0, P8 0003983b, P9 00000000, P10 NIL. > > I made some tests yesterday (including starting manually that stored > procedure mentioned before and starting the job manually) with no errors. > At the time when agents stops I can see no scheduled task set. > Could be that this job is scheduled "when idle" and finding out idle > condition raise an exception? > > thanks. > I already have done that and things are back to normal. Thank you.
Adrian Ciuca Show quote "Ciprian Gerea [MSFT]" <Ciprian.Ge***@online.microsoft.com> wrote in message news:eP3X%23filGHA.4708@TK2MSFTNGP04.phx.gbl... > Unfortunately you are hitting a bug that we knew about and that is going > to be fixed in SP2. Like you hinted, having jobs on CPU idle will crash > Agent. There's no workaround 'till then, so you might want to consider > changing the schedule to a predefined time window when you know the server > is not under significant load (e.g. 2 to 4 am) if that is at all possible. > > -- > Ciprian Gerea > SDE, SqlServer > > This posting is provided "AS IS" with no warranties, and confers no > rights. > > > "Adrian Ciuca" <no_spam_ciucaadrian@pcnet.ro> wrote in message > news:uJHR1mPlGHA.1344@TK2MSFTNGP03.phx.gbl... >> Ok, that suspicious job I was talking about is just a calling for a >> stored procedure wich is called also in other programs during normal >> operations. It cannot cause an AV. Other jobs are just doing backup >> operations during the night. >> This time I have the following errors cronologically at one second >> distance one from another: >> 1st: >> >> Exception 5 caught at line 431 of file >> t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent >> initiating self-termination. >> (both windows and sql event log) >> >> 2nd: >> >> EventType sql90exception, P1 sqlagent90.exe, P2 2005.90.2047.0, P3 >> 443f5c88, P4 sqlagent90.exe, P5 2005.90.2047.0, P6 443f5c88, P7 0, P8 >> 0003983b, P9 00000000, P10 NIL. >> (windows event log) >> >> and this is as information in windows event log: >> >> Bucket 05385461, bucket table 5, EventType sql90exception, P1 >> sqlagent90.exe, P2 2005.90.2047.0, P3 443f5c88, P4 sqlagent90.exe, P5 >> 2005.90.2047.0, P6 443f5c88, P7 0, P8 0003983b, P9 00000000, P10 NIL. >> >> I made some tests yesterday (including starting manually that stored >> procedure mentioned before and starting the job manually) with no errors. >> At the time when agents stops I can see no scheduled task set. >> Could be that this job is scheduled "when idle" and finding out idle >> condition raise an exception? >> >> thanks. >> > > I'm also getting the error every time I reboot SQL 2005 SP1. So is this a MS
Bug, if so is there a KB or a MS article that talks about it. Thanks, For the help, Nick 8[298] SQLServer Error: 2812, Could not find stored procedure 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs) Show quote "Ciprian Gerea [MSFT]" wrote: > Hi Adrian, > > The error about the call to xp_sqlagent_notify is a benign startup error, > and we're looking at fixing it for the next service pack. Without more > information I can't tell why agent is failing, but I believe that the job > history error that you're mentioning might give us a clue. > > [LOG] Exception 5 caught at line 431 of file > t:\yukon\sql\komodo\src\core\sqlagent\src\job.cpp. SQLServerAgent > initiating self-termination > > This says it's an acces violation that is likely caused by one of the jobs > that are running, so can you take a look at the jobs that you have on that > machine and see if any one of them has the potential to cause an AV? > > -- > Ciprian Gerea > SDE, SqlServer > > This posting is provided "AS IS" with no warranties, and confers no rights. > > > "BDB" <b**@reply.to.group.com> wrote in message > news:OHR1msVkGHA.3496@TK2MSFTNGP02.phx.gbl... > > > > "Adrian Ciuca" <no_spam_ciucaadrian@pcnet.ro> wrote in message > > news:%23RM%23awSkGHA.3496@TK2MSFTNGP02.phx.gbl... > >> My sql agent keeps failin with the following error: > >> Message > >> [298] SQLServer Error: 2812, Could not find stored procedure > >> 'msdb.dbo.xp_sqlagent_notify'. [SQLSTATE 42000] (DisableAgentXPs) > >> > > ... > > > > I'm having this same problem after installing SP1 and the current > > cumulative hot fix. I also posted on microsoft.public.sqlserver.server > > > > Bryan > > > > >  |
|||||||||||||||||||||||
Other interesting topics