Problem
I am a SQL Server Developer who recently migrated to a team with data integration production and monitoring functions. Everybody on the team uses SQL Server Agent, but I have practically no hands-on experience with it. Please give me a quick tutorial on creating a job, adding a schedule, and monitoring the operation of SQL Server Agent Jobs.
Solution
One especially fast way to ramp up to speed with SQL Server Agent is to use the Jobs and Jobs Activity Monitor icons under SQL Server Agent in Object Explorer within SQL Server Management Studio (SSMS). These icons open options for manually creating, configuring, controlling the operation of, and monitoring SQL Server Agent Jobs. While it is useful to program SQL Server Agent Jobs, it is not necessary to start deriving much value from it – especially during your initial introduction to it.
The tips covered here will focus on how to create and run SQL Server Agent Jobs. SQL Server Agent Jobs can be invoked on demand or on a schedule. You will initially learn about running a job on demand and later about how to assign a schedule to the job. The tip will also demonstrate how two SQL Server Agent Jobs can complement one another to satisfy a reporting requirement.
One of the most valuable manual features of SQL Server Agent is its Activity Monitor. You can use this feature to view the current status of a job as well as their past and prospective future performance on a SQL Server instance. This tip demonstrates selected menus from the Activity Monitor environment.
Creating your first two SQL Server Agent Jobs
To run a job on demand means the job has no schedule or is being run independent of the schedule. For example, you have to specifically invoke the job each time that you want it to run. One of the main advantages of SQL Server Agent is that it can schedule SQL Server Agent jobs. A job can be as simple as a T-SQL script. All the examples in this tip run T-SQL scripts. The first script creates a table in a database. The second script inserts a row into the table. By running the second job, we will be able to add a row to the table created in the first script.
To start your first SQL Server Agent job, open Object Explorer in SSMS and verify SQL Server Agent is running. The SQL Server Agent icon is available at the same level as the Databases icon. While Databases is the top icon underneath the Server icon in Object Explorer, SQL Server Agent is the last icon at the Databases level underneath the Server icon.
If the team to which you belong is regularly using SQL Server Agent, the feature will be on. However, if it is not on, you can right click the SQL Server Agent icon and choose Start. A green arrowhead next to the icon will indicate that it is on.
Right below the SQL Server Agent icon is the Jobs icon. You can use this icon to start a new job. Every job must have a name and at least one job step. All the jobs in this tip will have just one step. In the screen shot below, you see the SQL Server Agent icon and the Jobs folder below it.
Right click the Jobs folder and choose New Job to start a new job. This opens the New Job dialog. You can use this dialog to assign a name to the job. Additionally, you can add descriptive text about the job. See a completed example below of a New Job dialog with a name and description for a job.
Click the Steps page within the New Job dialog to present a dialog for adding a step to the job. Each step within a job designates what the step does. As with each job, every step within a job must have a name. Click the New button on the Steps page to assign a name to a step and indicate what the job step should do.
The following screen shot shows a completed Steps page. Notice that both the Step name and Command boxes have entries. The step name can reflect the role of the step. If you are using T-SQL to designate what the step should do, you can type or paste you code into the Command box. The code in the screen shot below assumes the prior creation of a database named for_SQL_Server_Agent_jobs. The code creates a fresh version of a table named RecordsNumericandDateTimeValues in the for_SQL_Server_Agent_jobs database.
The next two screen shots show key dialog boxes for creating a new job named Insert a value in a table. The table name is RecordsNumericandDatetimeValues in the for_SQL_Server_Agent_jobs database. The job has just one step that inserts 1 into the number_value column and the current datetime value into the date column of the table.
Run SQL Server Agent Jobs and Diagnose Performance
You can attempt to run a job by opening Job Activity Monitor with a View Job Activity menu selection, selecting the job, and invoking the Start Job at Step command from the context menu. The following screen shot shows the Job Activity Monitor just prior to the invoking of the Start Job at Step command for the Create a table job. The command will create a fresh copy of the RecordsNumericandDateTimeValues table in the for_SQL_Server_Agent_jobs database.
Next, we can invoke the Insert a value in a table job by selecting this job instead of the Create a table job. After successfully invoking both jobs, the RecordsNumericandDateTimeValues table will have one row in it that reflects the date and time when an insert was performed. The following screen shot shows a query and result set confirming the addition of one row to the RecordsNumericandDateTimeValues table.
It is possible for jobs in a production environment to fail for any of a variety of reasons. In the context of our example, the Insert a value in a table job can fail if the RecordsNumericandDateTimeValues table is dropped after the Create a table job is run and before the Insert a value in a table job is run. This can happen if some administrator or process inadvertently drops the table in the for_SQL_Server_Agent_jobs database. To show you how to diagnose a run-time failure, the RecordsNumericandDateTimeValues table was purposely dropped.
The following screen shot shows the outcome of trying to run the Insert a value in a table job when the the RecordsNumericandDateTimeValues table does not exist. Notice that an error is reported, but you are referred to the history log for additional details.
You can show the history log for a job step with two successive commands. First, select the job in Job Activity Monitor. Complete the first step by choosing the View history command from the context menu. The following screen shot shows the view just before invoking the View history command.
The second step involves selecting the job step with the failure in Log File Viewer. From this viewer, you can examine the message describing the error. In our example, the Message indicates the job failed because of an invalid object named RecordsNumericandDateTimeValues. Recall that the table with this name was manually dropped prior to invoking the Insert a value in a table job.
Adding a job with a schedule
Recall that one of the main advantages of SQL Server Agent is that it can run a job on a schedule. This section introduces you to the basics of adding a job that runs on a schedule.
Choose New Job from Jobs icon under SQL Server Agent. Assign a name to the job and optionally a description. The following screen shot shows the entry for a new job named Insert a value in a table with a schedule. A brief description is also assigned.
Next, select the Steps page. Click New towards the bottom of the page. Populate the Step name and Command boxes on the New Job Step page as indicated below. Then, click OK.
Next, select the Schedules page for the job. Then, click New.
- In the Name box type: Run daily every 5 minutes
- In the Schedule type drop-down box select Recurring
- In the Frequency settings,
- Choose Daily for the Occurs drop-down box and
- Enter 1 day(s) for Recurs every
- In the Daily frequency settings,
- Choose the Occurs every radio button
- Enter a value of 5 and
- Select a value of minute(s) from the drop-down box
After completing the settings, click OK to enable the schedule for the job. Then, click OK to save all the settings for the job named Insert a value in a table with a schedule. These settings will create a job that inserts a value of 1 in the RecordsNumericandDateTimeValues table every 5 minutes throughout each day. The job runs every day of the week because of the settings in the Frequency settings of the schedule page. Because SQL Server Agent is a SQL Server feature, the execution of the schedule for a job depends on the SQL Server instance for a SQL Server Agent being operational.
Running a SQL Server Agent Job with a Schedule
The job created with the preceding steps cannot run unless there is a RecordsNumericandDateTimeValues table in the for_SQL_Server_Agent_jobs database. Because this table is currently deleted, you must invoke the Create a table job before launching the Insert a value in a table with a schedule job. You can invoke the Create a table job from Activity Monitor by selecting the job and choosing Start Job at Step.
Next, you can select the Insert a value in a table with a schedule job in Activity Monitor and invoke it in the same way. This will cause the job to run indefinitely unless a menu selection stops the job, some error condition is encountered, or the server suspends operation for any reason. Recall that the job runs every 5 minutes of every day. At some point in the future, you can validate the operation of the job. For example, you can select all the rows in the RecordsNumericandDateTimeValues table.
To demonstrate the operation of the job, it was invoked on April 9, 2017 at around 7:08 PM. The query and screen shot below with a result set for the first 14 executions of the job appear below.
- The first record inserted into the newly created RecordsNumericandDateTimeValues table was at 19:08 hours on April 9, 2017; hours are represented on a 24-hour per day basis.
- A second record was inserted just after 19:10 hours, the commencement of the first 5-minute interval within an hour after 19:08.
- About every 5 minutes to within milliseconds, an additional record is inserted into the table.
Creating a job to summarize the outcome from another job
While it may sometimes be helpful to see the results for each operation of a job, it is also valuable to obtain a more summarized view of how an application is working. The next example illustrates how to provide this kind of capability with a new table and a new job. Instead of viewing the outcomes from the Insert a value in a table with a schedule job for each 5-minute interval, we can count the entries in the RecordsNumericandDateTimeValues table for each day. For example, the following query counts the number of rows entered into the table on each day.
SELECT CAST([date] AS date) [date], COUNT(*) [Number of Inserts]
FROM [for_SQL_Server_Agent_jobs].[dbo].[RecordsNumericandDateTimeValues]
GROUP BY CAST([date] AS date)
Assume that a requirement is issued for this kind of summary information. The requirement is for summary information being updated through midnight on each day. A SQL Server Agent job can automate the implementation of this capability through a scheduled job.
First, we need a new table into which to save the summary information. The following screen shot displays the sole job step for the Create a summary table job. As you can see, the job step’s name is Create a fresh summary table. The T-SQL in the command box performs these functions.
- Switch context from the default master database context to the for_SQL_Server_Agent_jobs database.
- Drop any previously existing version of the InsertsPerDay table in the database if one exists.
- Create a fresh version of the InsertsPerDay table with two columns: one named date and a second named Number of Inserts.
- Finally, the job step code specifies a primary clustered index on the date column. This is appropriate because the table is meant to have just one row per date value with the number of inserts for the date value on a row.
This Create a summary table job does not have a schedule. It is designed to operate on demand whenever there is the need to create a fresh copy of the InsertsPerDay table in the for_SQL_Server_Agent_jobs database.
The next screen shot shows the Freshly populate the InsertsPerDay table job step dialog from the Populate the InsertsPerDay table job. This job uses a different approach to reference a table in a job step than previously covered job steps. Instead of switching the context from the master database to the for_SQL_Server_Agent_jobs database, the code leaves the default database context to its default value of the master database. References to tables include a database name qualifier pointing at the for_SQL_Server_Agent_jobs database.
The job step has just two purposes.
- It initially truncates the InsertsPerDay table in the dbo schema of the for_SQL_Server_Agent_jobs database.
- Next, the job step uses an INSERT statement to populate the InsertsPerDay table with the result set from a query that counts the number of inserts per date value for all rows in the RecordsNumericandDateTimeValues table. These inserts are originally generated by the Insert a value in a table with a schedule job; see the Adding a job with a schedule section above to review this job.
In order for the Populate the InsertsPerDay table job to refresh the InsertsPerDay table at midnight on each day, you can specify a schedule like the one displayed in the screen shot below. The schedule name for this job is Daily at midnight. This schedule can be added to the Populate the InsertsPerDay table job in the same way that a schedule was added to the Insert a value in a table with a schedule job.
Notice that the job operates every one day just like the schedule for the Run daily every 5 minutes schedule for the job step in the Insert a value in a table with schedule job. However, one important difference is that this second schedule operates just once per day at midnight (12:00 AM).
Validating the Create a summary table job over three days
The Create a summary table job populates the InsertsPerDay table with the number of inserts on a daily basis into the RecordsNumericandDateTimeValues table. Recall that the Insert a value in a table with a schedule job populates the RecordsNumericandDateTimeValues table. A test run of these two jobs was conducted starting on 2017-04-09 19:08:21.340 and ending on 2017-04-12 03:30:00.990. Over this time span, there are three midnights (00:00:00.000) – one each on April 9, 10, and 11. An exogenous event caused the computer to halt operation after the last insert into the RecordsNumericandDateTimeValues table at 2017-04-12 03:30:00.990.
The following query returns the contents in the InsertsPerDay table for dates from 2017-04-09 through 2017-04-11. The following screen presents the result set from the query. The number of inserts on the tenth and eleventh of April cover 24-hour periods. Notice there are 288 inserts on each of these days. Within a 24-hour period, there are 288 5-minute intervals (12 per hour times 24 hours).
-- InsertsPerDay from 2017-04-09 through 2017-04-11
SELECT [date]
,[Number of Inserts]
FROM [for_SQL_Server_Agent_jobs].[dbo].[InsertsPerDay]
WHERE
CAST([date] as date) ¡Ý '2017-04-09'
AND CAST([date] as date) ¡Ü '2017-04-11'
Another way of validating the results is to count independently the rows per day in the RecordsNumericandDateTimeValues table for each day from 2017-04-09 through 2017-04-11. The following three queries show the independent query for each day from 2017-04-09 through 2017-04-11. The screen shot below the query listings display the three corresponding result sets. Notice that the count for each day independently from the RecordsNumericandDateTimeValues table match exactly the values from the InsertsPerDay query.
-- Count of rows populated on 2017-04-09 in RecordsNumericandDateTimeValues
SELECT COUNT(*) [Rows populated on 2017-04-09]
FROM [for_SQL_Server_Agent_jobs].[dbo].[RecordsNumericandDateTimeValues]
WHERE CAST([date] as date) = '2017-04-09'
-- Count of rows populated on 2017-04-10 in RecordsNumericandDateTimeValues
SELECT COUNT(*) [Rows populated on 2017-04-10]
FROM [for_SQL_Server_Agent_jobs].[dbo].[RecordsNumericandDateTimeValues]
WHERE CAST([date] as date) = '2017-04-10'
-- Count of rows populated on 2017-04-11 in RecordsNumericandDateTimeValues
SELECT COUNT(*) [Rows populated on 2017-04-11]
FROM [for_SQL_Server_Agent_jobs].[dbo].[RecordsNumericandDateTimeValues]
WHERE CAST([date] as date) = '2017-04-11'
Next Steps
- The SQL Server Agent tips covered here are very basic, but the tips are representative of the kinds of activities that beginning SQL Server Agent users are likely to invoke when creating and running single-step jobs. Furthermore, the tips include an introduction about how to examine and debug the operation of SQL Server Agent jobs.
- One critical element of a SQL Server Agent job is creating a schedule for when jobs run. The basics of assigning schedules to jobs are demonstrated in this tip.
- Two key jobs presented in this tip are the Insert a value in a table with a schedule job and the Populate the InsertsPerDay table job. These two jobs interact with each other in that the InsertsPerDay table job generates summary results on a daily basis for results from the Insert a value in a table with a schedule job. The tip closes with a validation of how the summary results from the InsertsPerDay table job are valid for inserts generated by the Insert a value in a table with a schedule job. Hopefully, you will find the demonstrations presented here helpful in implementing your own custom requirements.
