maximum number of pending SQLServerAgent notifications ?

maximum number of pending SQLServerAgent notifications ?

Post by Guy Rafalovic » Mon, 14 Oct 2002 20:40:14



Lately many of my jobs fail reporting the following error

The maximum number of pending SQLServerAgent notifications has been
exceeded.  The notification will be ignored. [SQLSTATE 01000] (Message
22022)  Unable to post notification to SQLServerAgent (reason: The maximum
number of pending SQLServerAgent notifications has been exceeded.  The
notification will be ignored.) [SQLSTATE 42000] (Error 22022)

After restarting the SQLServerAgent I get a short grace period with no
error, but after a few day they resurface.

Im running SQL2K  sp2 +latest hotfixes on win2k using netsend and email
(using outlook client ) notification.

Any idea to the cause ?

I couldnt find a signal article on the subject?

Please help

Guy Rafalovich

DBA Team Leader Hotbar.com

 
 
 

maximum number of pending SQLServerAgent notifications ?

Post by Gert E.R. Draper » Tue, 15 Oct 2002 08:02:22


This means you have more then 128 outstanding notifications, which can mean
you either can not process requests fast enough, which can happen if you
have a huge number of parallel jobs and did not adjust the sub system worker
thread limits. An other case where I have seen this happen is when people
had tight xp_sqlagent_enum_jobs or sp_help_job in a tight lopp, which blocks
the shared memory between the SQL Server and SQL Agent.

Post or email your SQLAgent.OUT file, there will be more clues in there I
hope, which one of the two cases. Make sure you are running with verbose
logging on, otherwise you might be missing out on a lot of information.


Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
You assume all risk for your use.
Copyright ? SQLDev.Net 1991-2002 All rights reserved.


Quote:> Lately many of my jobs fail reporting the following error

> "The maximum number of pending SQLServerAgent notifications has been
> exceeded.  The notification will be ignored. [SQLSTATE 01000] (Message
> 22022)  Unable to post notification to SQLServerAgent (reason: The maximum
> number of pending SQLServerAgent notifications has been exceeded.  The
> notification will be ignored.) [SQLSTATE 42000] (Error 22022)"

> After restarting the SQLServerAgent I get a short grace period with no
> error, but after a few day they resurface.

> I'm running SQL2K  sp2 +latest hotfixes on win2k using netsend and email
> (using outlook client ) notification.

> Any idea to the cause ?

> I couldn't find a signal article on the subject?

> Please help

> Guy Rafalovich

> DBA Team Leader Hotbar.com


 
 
 

1. The maximum number of pending SQLServerAgent notifications has been exceeded

hi all

my job file and i get this message

 Unable to post notification to SQLServerAgent
(reason: The maximum number of pending SQLServerAgent notifications has been
exceeded.
 The notification will be ignored.) [SQLSTATE 42000] (Error 22022)
 Associated statement is not prepared [SQLSTATE HY007] (Error 0)
 Unable to post notification to SQLServerAgent
(reason: The maximum number of pending SQLServerAgent notifications has been
exceeded.
 The notification will be ignored.) [SQLSTATE 42000] (Error 22022)

but their is no notification in this job

thank
shmulik

2. Delphi4 (TTreeNode.MoveTo)

3. The number of rows with pending changes has exceeded the set limit

4. +o Fairfield-CT - Development Programmer, Visual Basic, Visual C++, MCSE, Power Builder, OOD o+

5. Maximum number of databases per server?

6. Boston Sybase DBA

7. Maximum Number of Users

8. help: emtpy field

9. Maximum number worker thread in SQL

10. Maximum Number of Prefixes is 3

11. maximum number of bytes per row!!!

12. Maximum Number of Databases Accessed