SQL Server › SQL Server Agent & Job Scheduling

How do you create a basic SQL Server Agent job that runs a T-SQL script on a defined schedule?

Beginner

Answer

You create a new job, add one or more job steps defining the actual T-SQL command or script to run, and attach a schedule specifying how often and when the job should execute, such as daily at a specific time, letting SQL Server Agent handle triggering that job automatically going forward.

EXEC msdb.dbo.sp_add_job @job_name = 'CleanupOldLogs';
EXEC msdb.dbo.sp_add_jobstep @job_name = 'CleanupOldLogs', @step_name = 'DeleteOldRows',
  @command = 'DELETE FROM Logs WHERE LogDate < DATEADD(month, -6, GETDATE())';
EXEC msdb.dbo.sp_add_schedule @schedule_name = 'DailyAt2AM', @freq_type = 4, @active_start_time = 020000;

Real-world example An application automatically cleans up log entries older than six months every night at 2am using a scheduled SQL Server Agent job, keeping the logs table from growing indefinitely without any manual intervention.

Common follow-ups: Can a single job have multiple steps that run in sequence?;How do you view the history of when a job has run in the past?

Stored Procedures & Functions;Query Optimization & Plans