Maintenance Plan

—Maintenance plans create a workflow of the tasks required to make sure that your database is optimized, regularly backed up, and free of inconsistencies.

—

The Maintenance Plan Wizard also creates core maintenance plans, but creating plans manually gives you much more flexibility. 

Maintenance Plan helps us in automating tasks like

1. Checking DB Integrity.
2. Shrinking DB.
3. Reorganizing Index.
4. Rebuilding Index.
5. Updating Statistics.
6. Clean Up History.
7. Execute SQL Server Agent Job.
8. Backup Database.
9. Maintenence Cleanup Task.

We can create maintenance plan from SQL Server Management Studio by navigating to Management Folder them to Maintenance Plans and then right click and select "Maintenance Plan Wizard".

This will open a window shown below.

Say, Next.

Specify name for new maintenance plan say "Shrink DB" and we will select single schedule option as we are going to have only one task.

Select Checkbox against "Shrink Database", and click Next.

Now, Select DB you want to shrink.

Here you will option to DB size in MB which will trigger "DB Shrink" Operation, also wether freed space should go to OS or DB Files.

You can also get notified through email or can log to specific file location.

 

Say, Finish to Create maintenance Plan.