Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Are there limits on the number of SQL Agent jobs? Expand / Collapse
Author
Message
Posted Wednesday, August 15, 2012 5:35 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: 2 days ago @ 1:44 AM
Points: 654, Visits: 510
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
Post #1345216
Posted Wednesday, August 15, 2012 5:50 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Wednesday, October 22, 2014 7:41 AM
Points: 1,191, Visits: 9,887
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.
Post #1345221
Posted Wednesday, August 15, 2012 6:48 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, August 18, 2014 3:04 AM
Points: 1,331, Visits: 15,269
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.
Post #1345237
Posted Thursday, August 16, 2012 12:50 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: 2 days ago @ 6:08 PM
Points: 5,401, Visits: 7,514
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
Post #1346190
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse