How to Increase The maximum number of allowed pending SQLServerAgent notifications

Question

I have a large CI pipeline, it runs a series of deployments against a large set of databases(75 to be specific) on a (mostly) single SQL Server(2019). As part of this CI pipeline, and post deployment I am running a large number of database tests. These tests often create(and delete) SQL Agent jobs as part of there specific prerogatives. They also often invoke standard jobs belonging to a specific database. You can think of the process flow as the following:

1. Set some stuff up
2. Run a job
3. Wait for it to complete
4. Validate it some condition

These are being run in CI as the contents of the "setup" and "jobs" can change as the software itself is changes(we store database code in source control and it is deployed with each version of the software).

Now most(as far as I'm aware) of the jobs have notifications disabled, and the few that do have notifications enabled are ONLY sending "Failure" notifications:

enter image description here

So there shouldn't really be that many even with the 75 instance running not that many jobs are failing at the same time, further more we at a later stage prevent the server from sending emails(so suppress these alerts).

However, occasionally I do still receive the following error in my database tests:

Unable to post notification to SQLServerAgent (reason: The maximum number of pending SQLServerAgent notifications has been exceeded. The notification will be ignored.)

Typically I don't care, but this will cause my DB tests to occasionally fail polluting the results of my CI run.

How do I increase the number of allowed "Pending SQLServerAgent notifications", I don't care what the affect that this has on the server as long as "some day" the pending notification is made?

Is the only way to reduce or eliminate the existing notification configuration in all jobs? Or is there a configuration setting that can be changed here?

• nbk: you can flow control a ci so that ot doesn't happen and check the answer here dba.stackexchange.com/questions/237814/…
• David Rogers: @nbk I could, it would be great deal of effort, and then I would be making and supporting a way of running jobs that doesn't match what is being done further along in production, so when I changed what I was doing in the production job logic I would also need to modify whatever contrived thing I was doing in CI, lots of overhead, for what? To avoid the alteration of a single setting in SQL Server? That post is discussing(can be assumed) to be a app that will live at some point in a production environment, I'm not, I want to run the engine at 8000RPM's until it melts, that's my use case

Answer 1

A few details are lacking here, but it's quite likely that you are either e-mailing too many folks in the notifications or using multiple jobs to run the notifications simultaneously.

You can't increase this number, so you have to lower the number of notifications you are sending, try sending to groups instead of individuals, or build in delays so the alerts can be queued.

To be honest, I am a bit confused about why you are using jobs and job notifications to manage the CI pipeline here. Looking to some other tool to queue/flow your execution of steps ma be a superior answer. Redgate Software has tools to help with CI of database objects. Tools like SQL Sentry have job chaining. And you can possibly "pull" the status rather than push it.

• David Rogers: @MikeWlash yeah that's my bad, I didn't include enough information in my original post, I've updated it to give everyone a better idea of why I would want something like this. yes so I can modify the application to "remove" notifications if deploying against a CI server, that's a bit of a pain though, and since ALL notifications resulting from a CI run are already suppressed(ignored) anyways sort of duplicative, I was hoping for a easier way, notifications are a unwanted aftereffect of a CI run not a necessary part of it. Is there a official source stating that this parameter can't be changed?