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

By:   |   Updated: 2019-12-27   |   Comments   |   Related: More > Maintenance

Best Practices for SQL Server Maintenance

Free MSSQLTips Webinar: Best Practices for SQL Server Maintenance

Proper maintenance of a SQL Server requires several steps that need to run in harmony. From backups, corruption checks, index maintenance, data archiving, pruning system tables and more. Attend this free webinar to learn about best practices for SQL Server maintenance.


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.


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
--Creating the database
--Creating the TestTable table
   TestTableID INT IDENTITY(1,1),
   Val INT,
--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,

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:



  INSERT INTO TestTableHistory(TestTableID, Val, DateModified)
  SELECT TestTableID, Val, DateModified
  FROM TestTable

  DELETE FROM TestTable 







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:

maintenance plan tasks

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:

new maintenance plan

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

new maintenance plan

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

maintenance plan designer

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

maintenance plan execute t-sql task

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

maintenance plan execute t-sql task

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:

maintenance plan execute t-sql task

After clicking "OK" we set the schedule of our task:

schedule maintenance plan

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:

list of maintenance plans and jobs

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

execute maintenance plan

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

execute maintenance plan

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

SELECT * FROM TestTableHistory

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

query results

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:

maintenance plan tasks

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:

maintenance plan list

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.


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:

Last Updated: 2019-12-27

get scripts

next tip button

About the author
MSSQLTips author Sergey Gigoyan Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.

View all my tips
Related Resources

Comments For This Article


Recommended Reading

Deleting Historical Data from a Large Highly Concurrent SQL Server Database Table

Move SQL Server Maintenance Plan from One Server to Another

Update Statistics for All Tables and Databases in a SQL Server Instance

SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks

Getting Started with SQL Server Maintenance Plans - Part 1

get free sql tips
agree to terms