Create SQL Server Job to Run Periodic Tasks Automatically

Executing periodic tasks using SQL Server Agent by creating SQL jobs on SQL Server is one of the most powerful tools shipped with SQL Server data platform. SQL Server Management Studio serves the wizard for T-SQL developer and database administrators to create new SQL Server job in a few steps and configure properties like execution times by assigning a job schedule, logging job execution results, etc.

In order to create a new SQL job, first of all launch SQL Server Management Studio and connect to SQL Server instance.
On Object Explorer window expand node SQL Server Agent.
Under Jobs node, there are jobs already created on that SQL Server instance.

To create new SQL Server job, right click on the Jobs node and from context menu choose "New Job..."

create new SQL Server job

In General tab, type a descriptive short name for the new SQL job in Name textbox.
Additionally you can type informative detailed information in Description textbox.
Then press OK for next step.

define SQL job properties on SQL Server Management Studio

In Steps tab, SQL developers and administrators define the task which will be executed.
Click on New button to create a new SQL job step.

create new SQL job step to execute task

Type a step name.
I chosed Transact-SQL script (T-SQL) as step type.
T-SQL sql step type enables developers to execute SQL scripts like executing a stored procedure, etc. on the specified database.

For example, in Command text area I paste below SQL code.
In this tutorial, transfer_data is the stored procedure name.


Exec transfer_data

It is possible to validate the SQL codes by pressing the Parse button to see if there is an error preventing SQL job step to execute.
If everything is completed, press OK to continue with next step.

Transact-SQL script to execute task in SQLServer Job step

In the next step, the Schedules for the related SQL Server job is created and defined. You can click on New... to create a new schedule for periodic execution.

create job schedule to run peridically on SQL Server

In the New Job Schedule you can define details like frequency, execution days, time, start date and end date if an execution period exists.

If you have completed the SQL job schedule, press OK to complete the job creation.

SQL Server job schedule details

After SQL job is created, in the Object Explorer window of the SQL Server Management Studio you can see the recently created SQL job.

As seen in below screenshot, it is also possible to launch SQL job manually by right-click on the SQL Server job and choose "Start Job at Step..."

execute SQL job manually on SSMS