Search code examples
sql-serversql-server-2005sql-server-agentmaintenance-plan

SQL Server: should I use an "Agent Job" or a "Maintenance Plan" to delete old data?

I'm looking for a way to periodically (e.g. weekly) run some SQL statements in a database to delete old data. As far as I can see, there are (at least) two ways to do this:

  • using a "Maintenance Plan" and a "Execute T-SQL Statement Task"
  • using an "SQL Server Agent Job" and specify the statements in a "Step" of that job

My question is: what is the difference between these two possibilities and which one should I use for my task?

Solution

  • It's not really an either/or choice; there's some overlap.

    Think of a Maintenance Plan as a collection of steps to "do something" to your databases; those steps are encapsulated into a plan which needs to be scheduled to run.

    The SQL Server Agent is the service that periodically runs jobs; a job is anything that is scheduled to run. A Maintenance Plan is a job.

    When you schedule a Maintenance Plan to run, you are actually creating a job (or jobs; thanks DJ) for the SQL Server Agent to run periodically.

    Now, as to choosing which way is best (to go through the Maintenance Plan wizard or directly through the Agent), I would say that for most databases, the Maintenance Plan Wizard is suffecient. You may want to add additional steps to the job(s) created by the Maintenance Plan, but that depends on your environment.

    Does that make sense?