How to purge(delete) records using SQL Server Agent.

Problem: How to create a SQL Server Agent Job using Transact- SQL Statement.

Solution :

SQL Server Agent, is a service which runs on the instance specified to do the routine things automatically on the back ground for us. It can do wide range of things including running Transact-SQL scripts, command-line applications, Microsoft ActiveX scripts, Integration Services packages, Analysis Services commands and queries, or Replication tasks. It can generate alerts and send an email to update the status of the job as well. You can run a job manually, or you can configure it to run according to a schedule or in response to alerts.

For more information, please go through the links below for specifics,

Create Jobs

·         sp_add_job (Transact-SQL)

·         sp_add_jobstep (Transact-SQL)

·         sp_add_schedule (Transact-SQL)

·         sp_attach_schedule (Transact-SQL)

·         sp_add_jobserver (Transact-SQL)

Example:

In this article I am going to show you how to purge(delete) entries from a history table, I would like to delete rows based on entry date’s which are older than one year.

The below screenshot shows you how many of records in history table are older than one year.

SELECT count(1), getdate()  FROM [dbo].[History] where DATEDIFF(dd,[Entry_Date],getdate())>=365

Please see the sample script below to create a job, add a step and schedule it.

Please note you must start the SQL Server Agent Service to run the job. 

USE msdb ; 

GO 

EXEC dbo.sp_add_job  @job_name = N'Purge History' ,@owner_login_name=N'sa'; 

GO 

EXEC sp_add_jobstep 

    @job_name = N'Purge History', 

    @step_name = N'Deleting history records', 

    @subsystem = N'TSQL', 

    @command = N' DELETE  FROM [dbo].[History] where DATEDIFF(dd,[Entry_Date],getdate())>=365',

    @retry_attempts = 0, 

    @retry_interval = 0,

                @database_name ='DB'

GO 

EXEC dbo.sp_add_schedule 

    @schedule_name = N'RunDaily', 

    @enabled=1, --Enabled

                @freq_type=4, -- Daily

                @freq_interval =1, -- every frequent day

    @active_start_time = 194500, -- 7:45 PM

                @active_start_date=20170101,  -- starting January 1st 2017

                @active_end_date=99991231  ;  -- End date set to maximum

USE msdb ; 

GO 

EXEC sp_attach_schedule 

   @job_name = N'Purge History', 

   @schedule_name = N'RunDaily'; 

GO 

EXEC dbo.sp_add_jobserver 

    @job_name = N'Purge History'; 

GO 

Once we run this script on SSMS, you can see a job added to SQL Server Agent. 

You can view the status of the job, History of the job by clicking on view history. please see below history for the Purge History we just created.

You can see after the job has been run, there are no entries older than one year in History table.

SELECT count(1), getdate()  FROM [dbo].[History] where DATEDIFF(dd,[Entry_Date],getdate())>=365