SQL Server Maintenance Plan Execute T-SQL and SQL Server Agent Job Tasks

Problem

We have already discussed the SQL Server Maintenance Plan Wizard and the Maintenance Plan Designer in my previous articles, and have configured several maintenance tasks using these tools. All maintenance tasks discussed before in these series of articles, aimed at solving specific, standard problems, such as backing up databases, rebuilding indexes and so on. Sometimes, however, it is needed to configure a custom task. Actually, while almost all maintenance tasks are useful for solving a typical, common problem, two of them allow us to configure custom tasks. These two tasks are Execute T-SQL Statement and Execute SQL Server Agent Job tasks. In this article, we will explore how to design non-standard maintenance tasks using these two tasks.

Solution

In our examples, we will define two tasks that cannot be handled with the standard maintenance tasks and will solve these issues by using Execute T-SQL Statement and Execute SQL Server Agent Job tasks.

Execute T-SQL statement task in SQL Server Maintenance Plan

Let’s define a sample database maintenance example which cannot be configured by the standard maintenance tasks.

Our task is that we should periodically remove old data from one of the tables in our database and move this historical data to another table.

First of all, we should create our test environment:

USE master
GO
 
--Creating the database
CREATE DATABASE TestDB
GO
 
USE TestDB
GO
 
--Creating the TestTable table
CREATE TABLE TestTable
(
   TestTableID INT IDENTITY(1,1),
   Val INT,
   DateModified DATETIME DEFAULT GETDATE()
   CONSTRAINT [PK_TestTableID] PRIMARY KEY CLUSTERED (TestTableID ASC)
)
GO
 
--Insert data into table
INSERT INTO TestTable (Val, DateModified) 
VALUES(10, '2019-02-04'), (20,'2019-08-05'),(30, '2019-02-04'), (40,'2019-08-05'),(50, '2019-09-09'), (60,'2019-10-05'), (70, '2019-10-07')
 
--Creating the table for storing the historical data
CREATE TABLE TestTableHistory
(
   TestTableHistoryID INT IDENTITY(1,1),
   TestTableID INT,
   Val INT,
   DateModified DATETIME,
   CONSTRAINT [PK_TestTableHistoryID] PRIMARY KEY CLUSTERED (TestTableHistoryID ASC)
)
GO

The code above creates the TestDB database with two tables: TestTable and TestTableHistory. The TestTable is our regular table and the TestTableHistory is aimed at storing the historical data from the TestTable.

In our example, we need to delete the records older than one month from the TestTable and store them in the TestTableHistory table. The code below solves this task:

USE TestDB
GO
 
BEGIN TRY
  BEGIN TRANSACTION
  INSERT INTO TestTableHistory(TestTableID, Val, DateModified)
  SELECT TestTableID, Val, DateModified
  FROM TestTable
  WHERE  DATEDIFF(MONTH, DateModified, GETDATE()) > 1
  DELETE FROM TestTable 
  WHERE  DATEDIFF(MONTH, DateModified, GETDATE()) > 1
  IF @@TRANCOUNT > 0
     COMMIT
END TRY
BEGIN CATCH
  IF @@TRANCOUNT > 0
     ROLLBACK
  SELECT ERROR_MESSAGE()
END CATCH

We need to create a maintenance plan to execute this T-SQL query periodically. It can be done by using the Execute T-SQL Statement task, which simply executes the provided T-SQL code. This task cannot be found in the tasks’ list of the Maintenance Plan Wizard:

However, we can find this task in the Maintenance Plan Designer. To start it, we right-click on the “Maintenance” under the “Management” in SSMS:

We choose a name for our plan and click “OK” after which the Designer will be opened:

Here, in the “Toolbox” we can find Execute T-SQL Statement task:

To use it, we can just drag and drop into the design surface:

We can configure the task by double-clicking on it:

As a result, only one window is opened, where we can type our T-SQL statement. Thus, we have copied the code of moving data between tables and pasted it here:

After clicking “OK” we set the schedule of our task:

We have set it to run monthly (every first day of the month) at 1:00am. We have clicked “OK” and then, “Save” to save our task. Hence, we can see our plan in the list of the Maintenance Plans and the corresponding job under the SQL Server Agent Jobs:

To execute the task, we can right click on the Maintenance Plan and select Execute or running the corresponding SQL Server Agent Job:

After having the task successfully completed, we can check the results:

To see and test the results, we can run the following query and monitor the data movement from TestTable to TestTableHistory:

USE TestDB
GO
 
SELECT * FROM TestTable
 
SELECT * FROM TestTableHistory

As we can see, the records older than a month have been moved to the TestTableHistory table:

Hence, we can confirm that our task successfully performs its function.

Execute SQL Server Agent Job task

The Execute SQL Server Agent Job task executes a SQL Server Agent Job as its name suggests.

Executing a SQL Server Agent Job within a maintenance plan can sound a bit complicated. Actually, in the real world, is not too common. If it is needed to execute specific jobs, they can be simply created and scheduled directly in SQL Server Agent Jobs. However, in some specific cases, it may be needed. For instance, if we have a chain of tasks in our maintenance plan and before moving from one task to another, it is important to make sure that a job was executed. Therefore, an Execute SQL Server Agent Job task can be included between these tasks. Unlike the Execute T-SQL Statement task, the Execute SQL Server Agent Job task can be configured from both the Maintenance Plan Wizard and the Maintenance Plane Designer:

Let’s configure it using the Designer:

maintenance plan execute sql agent job task

For making our example simpler, let’s choose the RemoveOldData tasks corresponding job which is created in the previous example. It is important to mention that it is not possible to select multiple jobs to execute. If we click on “View T-SQL” we can see that the whole function of this task is executing the selected job. After saving the plan, we can see that it is added under the Maintenance Plans and a new job for it is created under the SQL Server Agent and we can successfully execute the task as in the previous example:

The question of why we need the Execute SQL Server Agent Job if we can use the Execute T-SQL Statement to solve the same problem could reasonably arise. The answer is that unlike the Execute T-SQL Statement, the Execute SQL Server Agent Job is not limited only to running T-SQL scripts, but Jobs can run other scripts, such as operating system scripts or PowerShell scripts.

Conclusion

Although the majority of the maintenance tasks can be helpful only for solving specific, standard problems, there are two tasks among them which are suitable for tackling customized tasks. These tasks are Execute T-SQL Statement task, that allows us to run custom T-SQL scripts within the maintenance plan, and SQL Server Agent Job tasks, which is aimed at running SQL Server Agent Jobs. Unlike the Execute T-SQL Statement task, which can be useful only for running T-SQL scripts, it is possible to run not only T-SQL but also other, such as PowerShell, scripts as a part of the job using the SQL Server Agent Job task.

Next Steps

To find more information about the discussed topic, please follow the links below:

Resources

Community

Subscribe