Monday, April 22, 2013

MS SQL Server Job

A SQL  Job is a collection of steps executed by the database engine by SQL ServerAgent.

The job can perform many different functions within it that can save time and effort on the part of employees.

Let us consider a procedure PrcDailyUpdateForecast which will be executing hourly.

Minimally, it takes two or three steps to set up a scheduled task to execute the code of your choice. First, go to the Management node, SQL Server Agent, and highlight jobs. Right-click, new job, and give it a name like UpdateForecast. Move to the steps tab, add a step called S1.

The first tab (General) of this dialog shows the name of the job, when the job was created, whether or not the job is enabled, the category the job falls into, who owns the job within the database and a description of the job.

To disable a SQL Server job, uncheck the Enabled checkbox and the job will no longer run in the database if it was scheduled to do so.

The Steps tab of this dialog brings up the following screen:

Each step within the job will be identified within this screen.

These steps can include executing data transformation packages, executing queries, executing stored procedures, etc.

Within this interface you can move the order of the steps around so that they occur in a specific order, as well as, add new steps or edit existing steps.

If you select a step and select the Edit button, the following screen appears:

This screen is used to set the command that is to be executed during the current step.

You can also specify the name of the step, the type of command to execute and the exit code of the process if successful.

The tab also has a Schedule tab that can be used to manage the schedule of theSQL Server jobs.

This will allow the job to be set to occur once a daily, multiple times per day, weekly, monthly or just a one time. The job can be set to run at a certain times per day on a recurring basis as well, such as every hour or at a certain time per day.

The following screen shows the scheduling tab:

To work with the schedule, click the New Schedule or the Edit button.

The New Schedule button will allow a new schedule to be input for the job.

The Edit button will allow the edit of the current schedule.

There is also a Notifications tab that can be used to handle notifications from theSQL Server in the event that something unusual occurs with the job while it is running.

The following screen shows the notification dialog which allows the selection of notifications options:

Notifications can be sent in multiple methods. For the notification to work, there must be operators set up within SQL Server to receive them.

The notification can be an email, a page or a net send. Each notification can be used to alert the operator that something has occurred on the server and the actions are controlled by SQL Server Agent.

Once the SQL Server job is configured, the changes can be saved and the job can now be used to perform routine tasks on a regular basis which can free up resources within your organization.


Post a Comment