11

I want to create a job which deletes records from a database after a period of time has passed. For example I have a field in news table Time Stamp and each month a SQL query runs like a scheduled job against my database and deletes news where the time stamp is two month old. Generally I want to delete news for 2 month ago and older to not let my table become a large table. How can I accomplish this?

krlmlr
  • 25,056
  • 14
  • 120
  • 217

2 Answers2

13

you should create a job in SQL below is a sample T-SQL for create a job via SQL agent

USE msdb ;
GO
EXEC dbo.sp_add_job
    @job_name = N'Weekly Sales Data Backup' ;
GO
EXEC sp_add_jobstep
    @job_name = N'Weekly Sales Data Backup',
    @step_name = N'Set database to read only',
    @subsystem = N'TSQL',
    @command = N'ALTER DATABASE SALES SET READ_ONLY', 
    @retry_attempts = 5,
    @retry_interval = 5 ;
GO
EXEC dbo.sp_add_schedule
    @schedule_name = N'RunOnce',
    @freq_type = 1,
    @active_start_time = 233000 ;
USE msdb ;
GO
EXEC sp_attach_schedule
   @job_name = N'Weekly Sales Data Backup',
   @schedule_name = N'RunOnce';
GO
EXEC dbo.sp_add_jobserver
    @job_name = N'Weekly Sales Data Backup';
GO
saeed
  • 2,477
  • 2
  • 23
  • 40
  • What is the purpose of N in `=N'...'` – xyz Mar 23 '15 at 10:45
  • @xyz The N in front of a text value indicates that the following text is of type nvarchar instead of varchar. For the difference between those two data types, see here: [link](http://stackoverflow.com/questions/144283/what-is-the-difference-between-varchar-and-nvarchar) – Otto Abnormalverbraucher Apr 28 '15 at 09:55
0

You will need to create a SQL Agent Job to schedule a job to periodically run. If you want to create the job with T-SQL, refer to How to: Create a SQL Server Agent Job (Transact-SQL).

Fls'Zen
  • 4,594
  • 1
  • 29
  • 37