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. In the window that appears click the New button. A dialog will appear as follows:
In the dialog you can select your database and enter T-SQL statements to perform the job. For example:
DELETE FROM PressCardQueue
WHERE DateReceived < GETDATE() - 14 --deleting records older than 2 weeks
or even a stored procedure:
EXEC MyStoredProcedure 'arg1', 'arg2'
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
Schedulesand click the New button. There are many options here to customize the run time to your liking:
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.