SQL Server jobs

In the day-to-day operations of running a business, employees can get caught up in more than one project, as is often the case.

We may be IT professionals but there is a limit to the amount of work that we can handle in one day and still stay on track.

With the amount of multitasking that employers feel their employees should be able to handle increasing with each passing day, the need to find a way to help themselves get the job done becomes increasingly more important.

Finding help and ways to get solutions into production even faster can make the day less stressful thereby making it more productive in the long run.

In the daily routine of a database administrator or a database developer, SQL Server has some functionality that can make life simpler and more dependable in the long run: SQL Server jobs.

How can the ability to create a job to handle routine tasks make the life of a database administrator or developer and how are SQL Server jobs created?

A SQL Server job is a collection of steps executed by the database engine by SQL Server Agent.

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

For example, a job can be created to import a daily update file internally or externally via an FTP server.

Another job can be configured to handle routine maintenance tasks as well as handling one-time production updates that may be needed in the future.

A job has many different options that can be configured during its creation in order to perform the duties that is needed of it.

In order to manage the jobs within a SQL Server database, use Enterprise Manager, and expand the Management node in Enterprise Manager.

Under the Management node is a node entitled SQL Server Agent. Expand this node and you will see the Jobs item that is used to represent the jobs within the system.

To see the jobs currently in the system, select Jobs. See the following illustration for an example of the steps described above:

The window on the left will show the jobs currently in the system. This window displays a lot of information about the SQL Server jobs currently in the system that can prove valuable.

The first column lists the name of the job that was entered when it was created.

The second column lists the category of the job that was assigned at the time of the job's creation. This helps to clarify the jobs and organize the jobs in the system.

The next column tells whether or not the job is enabled in the system.

If you wish to disable or enable a job in SQL Server, right click on the job and select on the Disable Job or Enable Job option from the popup menu.

The next column tells whether or not the job is runnable in the system.

After that there is a column, which tells whether or not the job is scheduled to run in the system.

The status column alerts the user to what is currently going on with the job. If the job is running it alerts the user to what step it is in currently.

The last run status column tells the status of the job’s last run and when that occurrence took place.

The final column shown tells when the next scheduled run is to take place.

To see the properties of a job in the window, right click on the job and select Properties from the popup menu or double click on the job. Either of these actions will bring up the following screen in order to set and/or view options contained in that job.

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 the SQL 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 the SQL 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:

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.

Return to SQL Server DBA