Right clicking on Maintenance Plan (partially behind the popup) allows you to choose the Maintenance Plan Wizard.

Building an SQL Maintenance Plan

Clicking on the <Select one or more> brings up the dialog where you choose which databases this will be run against as shown below.

This allows you to specify where reporting is done regarding this maintenance plan.  If you set up the mailing option described elsewhere on this site at https://www.erpsqlpro.com/sql-e-mail-setup.html <G> you can also have this e-mailed.  Click on Next once you have this filled in to bring up the screen that follows.

Continued at the top of the next column.

The general info screen comes up.  Click on the Next button.

This is the final screen before the plan is created.  You can click on the + next to any of the items to see the details.  Click on the Finish button and the plan is created as seen below in the second screen shot as well as the job a bit further down.  Thanks for visiting and I hope this helps!

As you can see, there are a lot of options here.  <G>  Pick the ones that suit and click on Ok.  Once a week during a time when no one is using SQL is good for most maintenance tasks.  Backups should be done at least once a day.  You should be back to the previous screen we saw at the bottom of the previous column.  Click on the Next button to select the maintenance tasks.

SQL server has a wizard to help with this.  Some screen shots and basic descriptions follow.  I would be happy to discuss setting this up for anyone this is all Greek to.  <G>

Select your favorite DBs and click on Ok.  Click on Next and the following screen will come up.

If I had chosen multiple things to do they would all be in the list and I could choose the order to run them in.  Generally they are already that way, so I usually just click through this screen.  Clicking on Next brings up the screen at the top of the next column.

Give your plan a name and a description if you like.  You can set up each task to have a schedule (Backup every day, rebuild indexes once a year, reorganize them once a week, etc.) or just have one schedule for everything, which is what we will set up for this one.  You can run the wizard to create additional plans on different schedules as well if you later decide they are necessary.  Clicking on the Change button will bring up the screen below where you can set your schedule.

This is a list of items you might like to do.  As you highlight each one a short description appears below it. Some additional info on these below.

The Check Database Integrity task performs internal consistency checks of the data and index pages within the database.  Pretty self explanatory.

​​The Shrink Database task reduces the disk space consumed by the database and log files by removing empty data and log pages.  SQL Server takes large chunks of disk space and after it uses it, grabs more.  Shrinking the DB give back anything above a certain amount.  The general recommendation on this is to not do it and you are just giving and taking the same space.

​The Reorganize Index task defragments and compacts clustered and non-clustered indexes on tables and views. This will improve index-scanning performance.  You would typically either do this or Rebuild Index on a regular basis.  The Reorganize task can be run while you are working while it is probably better to run the Rebuild task when no one is in as it will temporarily lock the tables it is using unless you have the Enterprise version of SQL.

​The Rebuild task reorganizes data on the data and index pages by rebuilding indexes. This improves performance of index scans and seeks. This task also optimizes the distribution of data and free space on the index pages, allowing faster future growth.  SQL essentially deletes and recreates the indexes when you do this, which reorganizes it and also puts it back together physically on the drive.

The Update Statistics task ensures the query optimizer has up-to-date information about the distribution of data values in the tables. This allows the optimizer to make better judgments about data access strategies.  SQL already updates these on the fly, so a coin flip on whether to do this or not.  It would depend on other items.

The History Cleanup task deletes historical data about Backup and Restore, SQL Server Agent, and Maintenance Plan operations.  This wizard allows you to specify the type and age of the data to be deleted.  This is removing the various files that keep track of what you've done.

​The Execute SQL Server Agent Job task allows you to select SQL Server Agent jobs to run as part of the maintenance plan.  The way this all works is that Jobs are created and the SQL Server Agent runs them.  If you wanted to create a custom step and run it as part of the plan, this allows you to do that.

The Back Up Database (Full) task allows you to specify the source databases, destination files or tapes, and overwrite options for a full backup.  This is the standard backup that I hope you are all running now.  <G>

​The Back Up Database (Differential) task allows you to specify the source databases, destination files or tapes, and overwrite options for a differential backup.  This basically is a backup of everything done since the last full backup.  This might be run several times a day to provide a shorter recovery in case of a failure.

​The Back Up Database (Transaction Log) task allows you to specify the source databases, destination files or tapes, and overwrite options for a transaction log backup.  If your recovery model is set to Full, you should be doing this on a regular basis and probably several times a day.  The advantage of setting the recovery model to full is that it allows point in time recovery of your database.  One often overlooked side effect is that unless you back up the log files, it will never let go of the transactions and will continue to grow forever.  

The Maintenance Cleanup task removes files left over from executing a maintenance plan.  This is typically used to keep x number of copies of various files.  For example I could set this to remove all files with the .bak extension that are older than 2 days.  If I back up daily, this would mean I always have the 2 newest backup files.

Once you have chosen your options, click on the Next button.  The only task I have  chosen is the Reorganize Index task.  Each task has its own options, which might be the topic of a future page.