Scheduling SQL Jobs
Scheduling SQL jobs can be very useful, both for repeating jobs, and for long-running jobs that execute during off hours.
While SQL jobs can be run using the command OSQL, or the Windows scheduler, there are some advantages to using the SQL Server Agent, and its built in job scheduler.
One of the biggest advantages of using the SQL Server Agent, is that jobs can consist of several steps. When one step completes, the job can go on to the next step, or end, depending on the outcome of the step that just finished.
For example, you could build a job that checks for new orders. If any new orders are found, you could proceed to a second step, and process the new orders. If none are found, you could end the job. Jobs can also proceed if a step fails, and end if it succeeds.
Maintenance tasks, such as backing up your databases, rebuilding indexes, are prime candidates for this type of scheduling. A single job can be created, that goes through all of the maintenance tasks. This job can then be scheduled to run periodically, during off hours, reducing the load impacting your users.
Another good candidate for this, would be a major reorganization of your database. Adding columns to large tables, and populating them with data, can put a load on your server. In this case, the job would be setup, and scheduled to run one time during off hours.
Any SQL commands can be run, as long as there is no user intervention needed. So, take a look through your SQL scripts, especially the ones that you need to run periodically, and consider scheduling them to run automatically.
Contact CCS Retail Systems if you need help creating you SQL scripts and jobs.