August 31, 2007 at 9:47 am
Wouldn't it be nice if MS provided us a way to disable individual steps on a job, the same way you can disable individual schedules?
Here's a situation I've run into several times and this is only an example for my above comment. It's not a problem I actually need solved because I've already resolved the issues I was having.
I have a massive job that has several steps. Some steps are running SSIS packages, others are T-SQL steps and others still are Operating system commands. I have a problem in particular with an OS command that does an FTP, but this FTP occurs in the middle of the job instead of at the end. So, while I could start the job at Step X, the job would execute all the steps after the FTP step, which I don't necessarily want.
My only option at this point is to create a new job with only the FTP step in it. No big deal in this case. But what if I actually *do* want to run some (not all) of the other steps in the other job?
Well, being able to disable certain steps in the job would come in handy, I would think. And why wouldn't MS be able to code the job to skip the disabled steps and move onto the next one (assuming you don't have one step pointing directly to the disabled step at which point it should fail the job)?
I'm doing a poll (sort of) to see how many people would get use out of such a capability. Let me know what you think of the idea and if you've ever run into a situation where such abilities would be useful. Also, let me know if you've had any work arounds short of deleting job steps or scripting out the job and recreating a shortened version of it.
Lastly, do you think I should fill in a suggestion form at MS for this idea?
September 4, 2007 at 11:27 am
They do, there just isn't something as simple as a disable checkbox on the step. To go about disabling job steps do the following:
The job will then prompt you that certain steps are unreachable in the execution plan which is exactly what you want.
Now, let's say you want to skip the first step in the job, that's just as easy. Do the following:
Again, not as easy and straight forward as a disable checkbox or something along those lines, but this is actually easier to use if you want to skip a large amount of steps in your job.
HTH,
John Eisbrener
September 4, 2007 at 11:34 am
Okay, now I feel just plain silly. @=)
I completely forgot about that functionality... Thanks for reminding me, John.
September 5, 2007 at 5:28 am
What would be FAR better is for Agent to support dependencies between jobs, and job suites.
With dependencies, you could chain jobs together, so that job B automatically starts after job A ends. It should also support an error stream, so that job C would be run if A ends in error. Oh, and also support running jobs in parallel.
Job suites provide a container that can be scheduled as an object. e.g. start the Backup suite at 21:00, run 4 jobs in parallel until they all complete, then start a User job suite.
You can almost visualise the design tool needed for this... it would look very similar to the BI Studio tool.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 5, 2007 at 5:35 am
Well, the agent does support that sort of thing much in the same manner as John mentioned the disabling of steps. What you do is use the sp_start_job proc to start jobs based on the failure or success of previous steps.
Unfortunately, that methodology only takes you so far. But you can do it with Jobs B & C based on the success or failure of Job A. In fact, we've applied that logic to several of our jobs at my current workplace because we do have job dependencies, but want to keep them in their own separate job "containers" to make tracking down problems a bit easier.
September 5, 2007 at 5:45 am
It is (relatively) easy enough to add extra steps to the end of each job that would interrogate some schedule information and start the next job in sequence, but it would be far more useable if this functionality was built into the Agent scheduler.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 5, 2007 at 6:51 am
I like the idea of disabling job steps. As mentioned it is possible to do this by changing the success action, but I think much cleaner to just temporarily turn off the job step. Doesn't alter the original flow at all. Though I would want SSMS to flag the job visually somehow that a step as disabled.
September 5, 2007 at 7:11 am
I definitely would like to just go in and click a little check box next to each job step to say "Disabled". It would make my life much easier. And a different visual for a "partially disabled" job would be cool to, so you could see it from the jobs list or the activity monitor.
Ed, as far as your container thing goes, are you referencing a particular third party tool that you've worked with which does things like this? Or are you just referring to a container setup like SSIS where you can call packages within packages and use precedence constraints and the like?
September 5, 2007 at 7:54 am
I have used 'full function' enterprise job schedulers in the past, such as IBM OPC/A and BMC CTRL-M. I have also seen Tivoli and CA job scheduling in action, but have not had any hands-on time with these. They all have the concept of a 'Job Suite' as a container.
In your example at the head of this thread, you would have created a job suite to do the processing of your complex job. This would have allowed the execution flow to split into parallel streams and rejoin as necessary. If you disable a given job you can set it to simulate a successful or failed run, and the suite will take whatever action you have configured when it hits that job.
Where you allow parallel streams (e.g. for database backups), you can set the maximum number of streams and the scheduler would start jobs as required to keep that number of streams active.
Most shops cannot afford the cost of an enterprise job scheduler, but it would take relatively little development by MS to make the Agent scheduler useful for the enterprise.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
September 5, 2007 at 8:34 am
The more I think about your idea, Ed, the more I like it. I'm not sure how easy or hard it really would be for MS to code it up, though. I've learned the hard way that the easier an End User thinks something is, the harder it is for the developer to actually get it written correctly. @=)
Anyone else have any thoughts on the job step disabling or Ed's Agent Scheduler ideas?
September 5, 2007 at 8:38 am
Effectively, there is a "job suite" as you put it, but not in the traditional sense. Because DTS and SSIS can call other DTS and SSIS packages and they also use the on success and on failure dependancies, you can effectively make a container package that references all the packages you want to run in succession or dependently on one another etc. It's a very broken fix to your problem, but the functionality is there. The only thing this doesn't afford you is a good reporting feature that shows you where in the "job" your package is currently in the execution or error phase.
I've consolidated big jobs into a single container DTS package before, but I've found DTS to be very buggy the larger or more complex you make it, so this isn't an ideal solution in SQL 2000. I haven't tried this approach with SSIS packages, but due to it's robust nature, I'm sure it would be better able to handle this type of functionality.
Just my two cents,
John
September 5, 2007 at 8:44 am
Except for the problem that the job is what actually schedules the SSIS package, so using SSIS to schedule the job wouldn't work except in a very recursive, clumsy way.
A bit like pulling the horse with the cart. @=)
September 5, 2007 at 9:02 am
Every job has an initial start off point, otherwise it wouldn't be a scheduled job, but an infinite loop of execution. The SSIS or DTS package would act as a container for all the logic, dependencies, notifications, failures, success, etc in the "job", but would be initially kicked off via a SQL Agent job task.
September 5, 2007 at 11:04 am
SQL Sentry does basically what EdVassie is talking about. Excellent scheduling software for SQL IMO. And no I am not linked with the company in any way, just been very succesfully running some systems with large quanitities of jobs using it.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply