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

Managing Jobs Part 3

By Andy Warren,

Jobs and job management, so much to talk about. I've already published two articles on the subject (Managing Jobs - Part 1 and Managing Jobs - Part 2), this week I'm going to cover some ideas about where, when and how to run jobs.

As soon as you get a request for any task that will run unattended and/or a scheduled basis, you need to think about not just how to solve the problem, but how you'll execute and monitor it. Let's talk about the second part first. What options do you have for scheduling?

  • SQL Agent (hey, we are SQLServerCentral!)
  • Windows Scheduler
  • Write your own
  • Buy 3rd party

In my experience you can solve most problems with the first two. SQLAgent has a tremendously flexible scheduling/job engine. You can support multiple schedules per job, has built in email notification, etc. The Win scheduler is good, but doesn't have the range of SQL Agent. More importantly to me, it logs the results to a text file and has no built in notification capability, so every app would have to report it's own success/failure.

For monitoring, I think you want to centralize as many of these tasks as you can. Ideally you want whatever runs the job to notify you if it fails and as I discussed in the earlier articles, you want a way to easily see all the failed jobs and to monitor the scheduler itself in case it fails (and so cannot notify you that it failed!).

To me (and I may well be biased) that means 99% of the time I will use SQL Agent for all jobs, not just stuff like backup, index rebuild, etc. Any screams from the readers yet? Not running that stuff on your production server? Good point. Once we've decided which scheduler to use, we need to decide where to run it. Some factors to consider:

  • Processing power and memory required for the task, disk space too
  • Bandwidth - don't want to run network intensive apps across a dialup connection, or even a slow frame link
  • Access - if you want it to run on a production server you'll probably have to go through an admin every time it changes
  • Overall machine load at the time it needs to run

A good first question is "will I gain a significant advantage in either process execution or administration by running directly on the db server?". In many cases you will. Simple TSQL jobs, even jobs that run far more complex stored procedures should go on the server. Even if you offload the scheduling part, the work still gets done on the server, so you don't gain much. Most db servers do peak workloads during the day, there is often a ton of availability capacity you can use. It's also a sturdy, well maintained machine. You won't have to worry about hardware failures, applying updates, virus protection, etc. All that stuff is in place already.

Just because you've decided on SQL Agent as your scheduler doesn't mean you have to use the one on your production server. Set up a reporting/task workstation, install SQL personal edition, just use the scheduler. Don't make the mistake of storing anything besides your jobs on the machine. It's limited to the number of connections it accepts (and only people administering it should be connected) and it's not running on a server quality machine. It still needs to be maintained - sql backup, index rebuild, file system backup. If you put a lot of stuff on this box and it breaks, trust me - you'll want to be prepared!

Eventually you'll run into a task that can't be (shouldn't be!) solved with just TSQL. Sometimes VBScript will do the job. If it will, good. Remember you have the 3200 character job step limit. More than that, you'll have to store your code in a file and execute it as an OS task using wscript.exe. Or you have purchased an executable that you just want to run. More likely, you'll have a task that goes way beyond that, something that requires a lot of business logic or faster execution than you get with compiled code. You probably also want to keep a job that big under source control, a task most easily done from a development environment.

In the next installment I'm going to show you some ideas about how to build your jobs as compiled VB/VB.Net code. Until then, I look forward to your comments on what I've written so far.

Total article views: 7113 | Views in the last 30 days: 5
Related Articles

scheduling and executing the dts packages

scheduling and executing the dts packages


SSIS package scheduled as job fails after about 20-40 minutes executing

SSIS Package fails after about 20-40 minutes executing


Process Task Not Executing when Scheduled

SSIS scheduled package will not execute


Executing SQL Server Jobs From An External Scheduler

SQL Server 2000 has a great scheduler in SQL Agent, but it isn't integrated with anything else. And ...


Sql server job scheduler question

Sql server job scheduler

sql server 7    
visual basic 6