Perform Scheduled Table Maintenance in SQL Server

Have you ever had a need to do routine maintenance on your database tables? For instance, moving older records to history, fixing anomalies, removing bad/incomplete data, updating flags under certain conditions, etc. These types of recurrent tasks can be implemented easily within SQL Server Management Studio (SSMS). With an SSMS component known as SQL Server Agent you can execute T-SQL statements on a repetitive schedule.

SQL Server Agent can be found at the bottom of the Object Explorer pane. Right-click on it and select New > Job. A window pops up to specify the name of your job with some other defaults. Notice the options on the left-hand side. This tutorial touches on the Steps and Schedules options.

Click on Steps. In the window that appears click the New button. A dialog will appear as follows:

SQL Agent Steps New Dialog

In the dialog you can select your database and enter T-SQL statements to perform the job. For example:
or even a stored procedure:

I recently created a job to maintain several tables. Then for each step I provided the T-SQL needed to do my maintenance on each table. Steps can be deleted individually and re-ordered.

Next, you need to set the schedule. Click on Schedules and click the New button. There are many options here to customize the run time to your liking:

SQL Agent Schedule New Dialog

Finally, save the job to make it live. And that’s it. You have created a table in an environment that can perform further automation. There is obviously many more options to explore in SQL Agent. This is just a taste.

Read More