Database task to delete table records periodcally in sql server 2008

Question

I want to add a database task that runs on a 6 hours interval. The task is to delete some records that matches certain condition.

What is the best way to achieve this in Sql Server 2008 ?

I know I can do this on the app side but I want it on sql server side.

Answer 1

Set up a task as maintenance task / scheduled task in the SQL Server admin.

Answer 2

You can use Sql server agent to run a job periodically. The job can do anything you want such as executing stored procedure which will do the actual cleaning.

Answer 3

There are several options. Two I would suggest:

1. Set up SQL Agent Job, with a T-SQL step in it. In the step details you will select target database and type in SQL statement to delete rows.

2. Create SSIS package; use Execute SQL Task and define SQL statement there. Then create SQl Agent Job to call the SSIS package.

(1) has a limitation - it will only be able to run the script against a database locate in the same instance of the SQl Server as SQl Agent, unless you use Linked servers.