SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Are there limits on the number of SQL Agent jobs?


Are there limits on the number of SQL Agent jobs?

Author
Message
Bob JH Cullen
Bob JH Cullen
Ten Centuries
Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)Ten Centuries (1.1K reputation)

Group: General Forum Members
Points: 1096 Visits: 786
Hi all -

We have a real-time system for checking the status of lone workers, and I currently create a SQL Agent job for each worker, set to run according to when the next check time is due (what happens then is not relevant to this enquiry). We have been happily using this with 50 or so workers, but wish to scale up to a possible maximum of several thousand.

I started to get uneasy about creating that many jobs, but couldn't find any hard information about limits imposed on job creation. I did find that only 60 jobs can be started in any one minute, which may yet see this approach ruled out, but I wondered if anyone has any words of wisdom about having many, many jobs on a server.

Thanks for all comments,
Bob
HowardW
HowardW
SSCertifiable
SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)SSCertifiable (5.9K reputation)

Group: General Forum Members
Points: 5875 Visits: 9892
I'm not sure if there's an absolute limit. I can see a few suggestions that there may have been in SQL 2000, but nothing concrete.

I would strongly reconsider this design though. It would be much more scalable to maintain a table with the schedules for all workers in it, then run a single Agent job every minute or so to check for any schedules that need processing at that time.
MysteryJimbo
MysteryJimbo
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4909 Visits: 15346
HowardW (8/15/2012)
I'm not sure if there's an absolute limit. I can see a few suggestions that there may have been in SQL 2000, but nothing concrete.

I would strongly reconsider this design though. It would be much more scalable to maintain a table with the schedules for all workers in it, then run a single Agent job every minute or so to check for any schedules that need processing at that time.


Jobs are all driven from msdb so the only limit imposed is likely to be maximum database size, but I doubt MS ever considered testing those billions of jobs. You will likely start getting issues with the sql agent service way before you reach the max db size as this will be running a timer loop and the more jobs it has to manage the bigger potential delay from scheduled start time.
Evil Kraig F
Evil Kraig F
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19409 Visits: 7660
This is inverted from the model I'm used to for a scenario like this. Usually either the worker, or a local monitoring software on the worker, will 'call in' to SQL (or preferably, a messaging queue) to deliver a status, instead of a polling from the SQL Server. In some cases specialized software goes out and does it for more maintainable customization. This allows for each component to be brought online/offline without server maintenance every time you stand up a new whatzit.

To the original question, no, there's no hard limit, but as mentioned, you'll probably run into an eventual optimization issue for the agent loop. I don't think a few thousand will get you there however unless you're running 10-20 unique schedules (or a few hundred) on each.


- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search