Creating SQL Server maintenance plan (Step by step procedure)

In this aritcle, I have explained, how you can esaily create SQL server maintenance plan by following few easy and simple steps given below, using an easy wizard-based process without any knowledge of T-SQL, but before we proceed further, here are the list of possible tasks which you can do within a database maintenance plan:

• Shrinking a database
• Backing up a database
• Performing an operator notification
• Updating database statistics
• Verifying the integrity of a database
• Cleaning up leftover maintenance files
• Executing a SQL Server Agent job
• Executing a Transact-SQL statement
• Rebuilding an index
• Reorganizing an index
• Cleaning up database histories

In this article example (which is showing SQL server 2008 based example), i will show you how to create new SQL Maintenance Plan that will contain few of the above options, you can either select these all options or any one or as per your requirements.

Note: If you are using SQL server Express version, you will not find Maintenance Plans under "Management", unfortunately there is no Maintenance Plans in free version of Sql server

Step by step procedure to create maintenance plan in SQL server

1. Open SQL Server Management Studio:
   • Open the SQLServer which contains the AdventureWorks2012 database
   • Open the Management folder then right click on Maintenance Plans.
   • Select New Maintenance Plan.
2. A new wizard will open click Next in the wizard 
   
3. Enter a Name for this maintenance plan. Optionally, enter in a description.
   
4. Click Change in the Schedule section to change the schedule. The Job Schedule Properties screen displays.
5. For example, you can set:
   • Schedule type to Recurring and select Enabled.
   • Occurs to Weekly.
   • Recurs every to 1 week(s) on then select the day of the week, for instance, Sunday.
   • Occures once at 0:00.
6. Click OK to close the job schedule properties, then click Next on the Select Plan Properties screen.
7. On the Select Maintenance Tasks screen, select the boxes shown with checkmarks below then click Next. 
   
8. Set the order of execution in the Maintenance task Order screen as shown below.
   • Use the Move Up and Move Down buttons to reposition the task (up or down) in the list.
   • When done click Next.
     
     The Define Database Check Intgerity Task window displays
9. Select All databases and select Include indexes in the Define Database Check Intgerity Task window.
   Note: In this example, I am selecting all the databases, you can select only the one you need.
   
10. Click Next. The Define Shrink Database Task window displays.
11. Do the following:
    
    • Select All databases, in the Databases field.
    • Set the Shrink database when it grows beyond to 50MB.
    • Set Amount of free space after shrink to 10%.
    • Select Return freed space to the operating system.
      
12. Click Next. The Define Reorganize Index Task window displays.
13. Select "All databases/All user database", in the Databases field and select Compact Large objects. 
14. Click Next. The Define Rebuild Index Task window displays.
15. Select All databases, in the Databases field and select Reorganize pages with the default amount of free space.
    
16. Click Next. The Define Update Statistics Task window displays.
17. Select All databases, in the Databases field and select All existing statistics and Full Scan.
18. Click Next. The Define Back Up Database (Full) Task window displays.
19. Do the following:
    • Select All databases, in the Databases field.
    • For Back up to:, select Disk.
    • Select Create a backup file for every database.
    • Choose the folder in which to save the backups, for example: C:\AllUserDatabaseBackups\
    • Select Verify backup integrity.
      
20. Click Next. The Define Maintenance Cleanup Task window displays.
21. Do the following:
    • For Delete files of the following type: select Backup files.
    • Select Search folder and delete files based on extension and select the folder you indicated in the step above, for example: C:\AllUserDatabaseBackups\
    • Enter bak for the File Extension.
    • Select Delete files based on the age of the file at task run time.
    • Indicate Delete files older than by the number of days or week you want to keep the back file(s).
      
22. Click Next. The Select Report Options window displays.
23. Select Write a report to a text file. For folder location choose a path to save the maintenance report files, for example: C:\AllUserDatabaseBackups\Reports, or choose the default location.
24. Click Next. The Complete the Wizard window displays.
25. This allows you to verify all steps of the maintenance task. When approved, click Finish.
    
    
26. After clicking the Finish button SQLServer creates the maintenance task. If everything is successful you can close the window.

You can execute the maintenance plan to verify it, by using following steps:

• In the SQLServer Management Studio window, open the SQLServer
• Open Management > Maintenance Plans
• Right-click the maintenance plan you created then click Execute.

How often to run jobs

This will vary depending on your needs, but here are some common guidelines for how often to schedule tasks.

User Databases

• Check Database Integrity – DAILY (or at least WEEKLY)
• Rebuild Index WEEKLY (DAILY is better). Choose this instead Reorganize Index and Update Statistics if you have enough time
• Reorganize Index and Update Statistics WEEKLY (DAILY is better) Only do this if no time for Rebuild Index
• Back Up Database (Full) WEEKLY
• Back Up Database (Differential) EVERYDAY EXCEPT FULL BACKUP DAY
• Backup Database (Transaction Log) – HOURLY
• Maintenance Cleanup Task – WEEKLY
• Shrink Database DO NOT SCHEDULE – ON DEMAND ONLY
• Execute SQL Server Agent Job – DEPENDS
• History Cleanup – WEEKLY (or other)