How to Maintain State in a SQL Server Agent Job

By: Ray Barley   |   Updated: 2020-03-12   |   Comments   |   Related: > SQL Server Agent

Problem

In my organization we make extensive use of SQL Server Agent Jobs to schedule and execute our processing. Our SQL Agent job steps typically execute SSIS packages. We have some SQL Agent jobs with many steps, sometimes as many as 50! We are trying to build our SSIS packages so they can easily be restarted in the event an error occurs. We experimented with checkpoints, but what we really need is to be able to save and retrieve the state of the SQL Agent job in any of the job steps. The state is simply any data that originates in a job step that may be needed in another job step.

We are looking for a generic solution, one that will work in any SQL Agent job without creating custom tables for each job. There does not seem to be a built-in standard way of doing this provided by the SQL Agent. Can you provide a solution?

Solution

In this tip I will demonstrate a simple way of implementing state for a SQL Agent job. The following is the high-level description:

• Create a STATE table to store the state of any SQL Agent job
• Create STATE stored procedures to initialize, retrieve, and update the state
• Add Execute SQL tasks in the SSIS packages to execute the STATE stored procedures as necessary

I will walk through the implementation in the sections that follow.  You can also download all of the code at the bottom of this article.

Demo Scenario

Before diving in to the implementation details, I have a really simple example that I will use as a demo. I want to build a data mart to analyze SQL Agent job history. I have a SQL Agent job that is scheduled to run periodically. The job executes the following SSIS packages in an SSIS project:

• INITIALIZE_JOB_STATE
• STAGE_INCREMENTAL_SQL_AGENT_JOB_HISTORY
• LOAD_ INCREMENTAL_SQL_AGENT_JOB_HISTORY

INITIALIZE_JOB_STATE

This SSIS package creates the initial JSON document that represents the SQL Agent job state and stores it in the STATE table. The state in my SQL Agent job is the following:

The CUSTOM_STATE is a JSON document that contains the state for the job. The values are the range of instance_ids to be loaded from the msdb.dbo.sysjobhistory table. Every time a SQL Agent job step completes, a row is inserted into the msdb.dbo.sysjobhistory table with an automatically incrementing instance_id. Every time my SQL Agent job runs, the INITIALIZE_JOB_STATE package determines the instance_id range of the rows added and saves it in the SQL Agent job state.

I chose to use JSON because it’s human-readable and easy to add, change or delete the contents. Beginning in SQL Server 2016, there are built-in JSON functions and operators; see JSON data in SQL Server for the details. I use the JSON_VALUE() and JSON_MODIFY() functions in this solution.

STAGE_INCREMENTAL_SQL_AGENT_JOB_HISTORY

This SSIS package retrieves the SQL Agent job state, extracts the rows from the msdb.dbo.sysjobhistory table based on the range of instance_id values in the state, and inserts the rows into a staging table.

LOAD_ INCREMENTAL_SQL_AGENT_JOB_HISTORY

This SSIS package gets the rows from the staging table and inserts them into the history table. Normally there would be some sort of transformations on the data in staging, but I skipped that because this is just a demo scenario.

Implementing State in a SQL Agent Job

I want a solution that requires a minimal amount of work, whether you want to retrofit this into an existing SQL Agent job or include this in a new SQL Agent job. Besides the initial creation of the tables and stored procedures, there are just a few steps that I will go through in this section.

Create the STATE Table

The following table is used to save that state of the SQL Agent job:

The main points are:

• Each SQL Agent job has its own row in the table identified by the STATE_ID column.
• To setup the state for a SQL Agent job, you execute the CREATE_STATE stored procedure once, providing a value for the NAME parameter. The stored procedure returns the list of rows in descending order of STATE_ID.
• Add a project parameter (e.g. JOB_STATE_ID) to your SSIS project and assign it the STATE_ID value. Every SSIS package in the project now has access to the JOB_STATE_ID project parameter. You pass the JOB_STATE_ID value to the stored procedures to initialize, retrieve and update the state.
• The CUSTOM_STATE column is where you store the state as JSON.

The following is an example of executing the CREATE_STATE stored procedure and the output:

Working with State

My implementation of state in a SQL Agent job has just three things that you can do:

1. Initialize state at the very beginning of the SQL Agent job.
2. Update state whenever there is data that you want to persist and make available to another SSIS package in the SQL Agent job.
3. Get the state when you want to access the data persisted by a previous step in the SQL Agent job.

I have stored procedures to perform the initialize, update, and retrieve operations. Since the actual state is stored as a JSON document, the stored procedures can be used in any SSIS package. You determine the state that you want to persist and specify it in the JSON.

INITIALIZE_STATE Stored Procedure

The INITIALIZE_STATE stored procedure is shown below:

The following are the main points:

• The @STATE_ID parameter value is given to you when you execute the CREATE_STATE stored procedure (see the Create the STATE Table section above). I store it in the project parameter JOB_STATE_ID.
• The @CUSTOM_STATE parameter contains a JSON document that represents the initial state for your SQL Agent job. It can be just about anything you want. The procedure stores whatever is in the @CUSTOM_STATE parameter without alteration.
• The procedure assumes that a row exists with the STATE_ID equal to the value you pass in the @STATE_ID parameter, but it will insert a new row if a row does not exist.

I used this stored procedure in the demo scenario to create the empty instance_id range of values as shown below:

UPDATE_STATE_INT_VALUE Stored Procedure

The UPDATE_STATE_INT_VALUE stored procedure updates an integer value in the JSON stored in the CUSTOM_STATE column. The UPDATE_STATE_INT_VALUE stored procedure is shown below:

The following are the main points:

• @STATE_ID is passed the value stored in the project parameter JOB_STATE_ID.
• @PATH identifies the name value pair in the JSON that you want to update.
• @INT_VALUE is the value you want to update.
• JSON_MODIFY() is a built-in function in SQL Server 2016 (and following) allowing you to update the value of a  name-value pair in the JSON document.

In my initial implementation, I am only storing integer values in the state. In the future I will add an UPDATE_STATE_STRING_VALUE stored procedure.

I used this stored procedure in the demo scenario to update the instance_id range of values in the JSON document in the CUSTOM_STATE column:

GET_STATE Stored Procedure

The GET_STATE stored procedure retrieves the JSON document stored in the CUSTOM_STATE column as an OUTPUT parameter to the stored procedure as shown below:

The following are the main points:

• @STATE_ID is passed the value stored in the project parameter JOB_STATE_ID.
• @CUSTOM_STATE gets the JSON document.

The following is an example of calling the stored procedure and returns the values in the JSON document as a normal result set:

The following are the main points:

• @STATE_ID is passed the value stored in the project parameter JOB_STATE_ID.
• @CUSTOM_STATE gets the JSON document.
• JSON_VALUE() is a built-in function that retrieves a value from a name-value pair in a JSON document.

SSIS Package Review

In this section I will provide a high-level overview of the SSIS packages in my demo scenario. The SSIS packages are executed in a SQL Agent job in the order I review them.

INITIALIZE_JOB_STATE

This package initializes the SQL Agent job state. In my scenario, the SSIS package executed in the first step of the SQL Agent job would do this. This following is the control flow for this package:

The main points are:

• INITIALIZE_STATE executes the INITIALIZE_STATE stored procedure (described above) to clear out / overwrite any previous state for the SQL Agent job.
• GET START JOB INSTANCE ID returns the first instance_id  in the msdb.dbo.sysjobhistory table to be extracted. It gets the MAX(instance_id) from my JOB_HISTORY table and adds 1. I want to start retrieving rows from the msdb.dbo.sysjobhistory table that are greater than or equal to this value.
• GET END JOB INSTANCE ID returns the MAX(instance_id) from the msdb.dbo.sysjobhistory table. This is the last row that I want to retrieve during this run.
• The UPDATE_STATE sequence container updates the job state executing the stored procedure UPDATE_STATE_INT_VALUE for each of the START JOB INSTANCE ID and END JOB INSTANCE ID values retrieved earlier in the package.

When this package ends, the state contains the range of instance_ids to be selected from the msdb.dbo.sysjobhistory table.

STAGE_INCREMENTAL_SQL_AGENT_JOB_HISTORY

This package executes the stored procedure GET_STATE to retrieve the range of instance_ids to select from the msdb.dbo.sysjobhistory table, truncates the JOB_HISTORY_STAGING table, retrieves the rows in the instance_id range, and inserts them into the JOB_HISTORY_STAGING table.

LOAD_INCREMENTAL_SQL_AGENT_JOB_HISTORY

This package loads the rows in the JOB_HISTORY_STAGING table into the JOB_HISTORY table. Based on the instance_id value, the rows to be loaded are checked that they do not already exist in the JOB_HISTORY table.

SQL Agent Job

The three SSIS packages I have just described in the preceding section are executed in SQL Agent job steps. The design of the job and the SSIS packages is such that if any step fails, you resolve the issue, and restart the SQL Agent job at the step that failed. As an example, if step 2 failed, the STATE table contains the range of instance_ids that are to be selected from the msdb.dbo.sysjobhistory table. After you resolve the issue, you can restart the job at step 2 and the appropriate rows will be retrieved and inserted into the JOB_HISTORY_STAGING table.

The STATE table allows you to manually set the state and rerun the job starting at a particular step. In my simple scenario, if you needed to load old data from the msdb.dbo.sysjobhistory table, you could do that.

Future Enhancements

I have a number of thoughts on enhancements. The most important one is to implement logic that performs the following by leveraging the STATE table:

1. Save the result of each SQL Agent job step in the STATE table. This would be success or failure and the error information.
2. Save the result of the SQL Agent job execution in the STATE table.  This would be success or failure and the error information.
3. When you start the job at step 1, make sure that the STATE table reflects that the last run was successful else raise an error and force the job to fail.
4. When you start the job at any step other than step 1, make sure that the STATE table reflects that the last run failed at that step or the previous step was successful. In other words, make sure that the step you are starting from is correct based on the STATE table. Fail the job if that is not the case.
5. Add an ignore previous failure parameter to the INITIALIZE_STATE stored procedure to allow starting from step 1 even if the previous run failed (i.e. override the logic in #3 above).

Another idea for an enhancement is to grab the data available via SQL Agent tokens. For instance, I would like to get the SQL Agent job_id, store it in the STATE table row, and to be able to retrieve data from the various SQL Agent tables. You can find an example of how to do this in our earlier tip Passing SQL Server Agent Tokens to an Integration Services Package by Koen Verbeeck.

Next Steps

• SQL Agent is a great tool. Check out the SQL Server Agent Tip category on MSSQLTips.com.
• Think about maintaining state in your SQL Agent jobs. It is a really good idea and may make your jobs more resilient.
• Download the code from this tip and experiment.

About the author

Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips