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:
My question is: what is the difference between these two possibilities and which one should I use for my task?
SQL Server Agent is a Microsoft Windows service that runs scheduled administrative tasks that are called jobs. You can use SQL Server Agent to run T-SQL jobs to rebuild indexes, run corruption checks, and aggregate data in a SQL Server DB instance.
The Maintenance Plan Wizard creates a maintenance plan that Microsoft SQL Server Agent can run on a regular basis. This allows you to perform various database administration tasks, including backups, database integrity checks, or database statistics updates, at specified intervals.
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?
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With