Logo
programming4us
programming4us
programming4us
programming4us
Home
programming4us
XP
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Windows Phone
 
Windows Server

SQL Server 2008 : Automating Routine Maintenance - Maintenance Plans

6/15/2011 9:30:02 AM
Maintenance plans enable you to set up a predefined set of routine maintenance tasks on your SQL Server instances. You can create a maintenance plan by using the Maintenance Plan Wizard or going straight to the Maintenance Plan Design tab. Just like all wizards in SQL Server, the Maintenance Plan Wizard is a pretty straightforward point-and-click operation. The Maintenance Plan Design tab however, gives you a few extra options and provides a little more control over the maintenance tasks. Maintenance plans are nothing more than SQL Server Agent jobs that execute SQL Server Integration Services packages.

1. Maintenance Plan Wizard

The Maintenance Plan Wizard provides a simple interface for you to define and schedule tasks that perform routine maintenance. Right-click the Maintenance Plans folder located under the Management node and select Maintenance Plan Wizard from the context menu. Click Next when the Welcome screen appears to display the Select Plan Properties page, as shown in Figure 1.

Figure 1. Maintenance Plan Wizard Select Plan Properties page

Name the maintenance plan and provide a brief description. The main thing to point out on this page is the scheduling options. If you select Separate Schedules for Each Task, a separate SQL Server Agent job will be created for each task, and each will be located in its own subplan. The scheduling option is disabled on this page, and you will need to provide a separate schedule when configuring each individual task. If you select Single Schedule for the Entire Plan or No Schedule, each task will be included in a single subplan using a single SQL Server Agent job, and the individual scheduling options for each task will be disabled. Click Next to display the Select Maintenance Tasks page, shown in Figure 2.

Figure 2. Maintenance Plan Wizard Select Maintenance Tasks page

You can view a brief description of each task by highlighting it. For example, you can see a description of the Update Statistics task that is highlighted in Figure 13-23. Select the tasks you want to include in the maintenance plan and continue through the wizard to configure each task.

2. Maintenance Plan Design Tab

You can use the Maintenance Plan Design tab to build a maintenance plan from scratch or use it to tweak plans created by the Maintenance Plan Wizard. One of the things that bothers us about the Maintenance Plan Wizard is the inability to rename the subplans during the setup process. As a result, you end up with SQL Agent jobs named MaintencePlanName.SubPlan_1, MaintencePlanName.SubPlan_2, and so on, for each task that requires a separate job schedule. This naming convention tells you nothing about a given job. For example, does it rebuild indexes, update statistics, back up a database, or what? So the first thing you should do after using the Maintenance Plan Wizard is open the Design tab and rename the subplans to something more descriptive.

To open a plan that has been created using the wizard, just double-click Maintenance Plan located in the Maintenance Plans folder under the Management node in the SQL Server Management Studio Object Explorer. To create a new maintenance plan using the Design tab, right-click the Maintenance Plans folder located under the Management node and select New Maintenance Plan. Enter a name for the new plan when prompted, and click OK. (The Maintenance Plan Design tab is shown in Figure 3.)

Figure 3. Maintenance Plan Design tab

If you have ever created a SQL Server Integration Services package, the Design tab should look extremely familiar. From here you can add, remove, and configure subplans (including giving them a descriptive name). Once you have configured the subplans to perform the appropriate maintenance tasks, save the maintenance plan to create or update the SQL Server Agent jobs that run them.

3. Maintenance Plan Tasks

Now that we have discussed how to create and edit maintenance plans, let's review the available maintenance tasks. There are two maintenance tasks available in the Design tab that are not available in the wizard: Execute T-SQL Statement Task and Notify Operator Task. You may find these two extra tasks useful if you want to perform more customized operations in your maintenance plans. The following is a list of tasks you can perform with maintenance plans.

  • Backup Database Task: You can use this task to perform a full, differential, or transaction log backup for one or more databases.

  • Check Database Integrity Task: You can use this task to execute the DBCC CHECKDB command against one or more databases.

  • Execute SQL Server Agent Job Task: You can use this task to select an existing SQL Server Agent job to run as part of the maintenance plan.

  • Execute T-SQL Statement Task: You can use this task to execute a custom T-SQL statement as part of the maintenance plan.

  • History Cleanup Task: You can use this task to clean up backup and restore history, SQL Server Agent job history, and maintenance plan history from the msdb that is older than a specified date.

  • Maintenance Cleanup Task: You can use this task to delete files such as database backups and maintenance plan text files that are older than a specified date.

  • Notify Operator Task: You can use this task to send a notification to an existing operator as part of the maintenance plan.

  • Rebuild Index Task: You can use this task to rebuild all indexes in one or more databases, or you can target specific objects for index rebuilds.

  • Reorganize Index Task: You can use this task to reorganize all indexes in one or more databases or you can target specific objects for index reorganization.

  • Shrink Database Task: You can use this task to shrink one or more databases, though you really should not have to shrink databases as a part of your maintenance plan. Constantly shrinking and growing your databases will greatly reduce performance.

  • Update Statistics Task: You can use this task to update statistics in one or more databases.

You can reproduce every maintenance task using T-SQL. There is no magic going on in the background. We are not against maintenance plans, but we tend to create our own maintenance jobs using T-SQL scripts. We like a little more control than the maintenance plan will allow. For example, instead of blindly rebuilding an index, we check the fragmentation level first. If the fragmentation level is between 5% and 30%, we reorganize the index. If the fragmentation level is greater than 30%, we rebuild the index. For more information on finding index fragmentation levels, search for "Reorganizing and Rebuilding Indexes" in SQL Server Books Online.

4. Exporting and Importing Maintenance Plans

Connect to Integration Services on the SQL Server instance that contains the maintenance plan you would like to export, expand the Maintenance Plans folder, right-click the maintenance plan and select Export Package, as shown in Figure 4.

Figure 4. Export Integration Services maintenance plan package

This will open the Export Package dialog box shown in Figure 5. You can use this dialog box to save the maintenance plan to another instance of SQL Server, or save it to the file system so you can import the maintenance plan on the new SQL Server instance later.

Figure 5. Export Package dialog box

If you are importing the maintenance plan from the file system, you will need to connect to Integration Services on the new SQL Server instance and right-click the Maintenance Plans folder and select Import Package to open the Import Package dialog box shown in Figure 6.

Figure 6. Import Package dialog box

Once you have imported the maintenance plan on the new instance of SQL Server, you will need to open it using the Design tab and click the Save button in order to create the jobs associated with the plan.

Other -----------------
- SQL Server 2008 : SQL Server Agent (part 2) - Jobs & Proxies
- SQL Server 2008 : SQL Server Agent (part 1) - Operators, Enabling SQL Server Agent Notifications & Alerts
- Windows Server 2008 R2 : Install Internet Information Services (part 2) - Install IIS on Windows Server 2008 R2 Core Server & Windows Server 2008 R2 Web Edition
- Windows Server 2008 R2 : Install Internet Information Services (part 1) - Understand Internet Information Services Role Services
- Microsoft Dynamics CRM 2011 : Sharing Accounts and Contacts with Other Users
- Microsoft Dynamics CRM 2011 : Deactivating and Activating Records
- Introducing SharePoint Mobile in 2010 (part 3) - Mobile Search & Mobile Views
- Introducing SharePoint Mobile in 2010 (part 2) - Microsoft Office Web Applications
- Introducing SharePoint Mobile in 2010 (part 1) - Microsoft Office Mobile 2010 & SharePoint Workspace Mobile 2010
- Migrate Roles to Windows Server 2008 R2
 
 
Top 10 video Game
-   Minecraft Mods - MAD PACK #10 'NETHER DOOM!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #9 'KING SLIME!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #2 'LAVA LOBBERS!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Minecraft Mods - MAD PACK #3 'OBSIDIAN LONGSWORD!' with Vikkstar & Pete (Minecraft Mod - Mad Pack 2)
-   Total War: Warhammer [PC] Demigryph Trailer
-   Minecraft | MINIONS MOVIE MOD! (Despicable Me, Minions Movie)
-   Minecraft | Crazy Craft 3.0 - Ep 3! "TITANS ATTACK"
-   Minecraft | Crazy Craft 3.0 - Ep 2! "THIEVING FROM THE CRAZIES"
-   Minecraft | MORPH HIDE AND SEEK - Minions Despicable Me Mod
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 92 "IS JOE DEAD?!"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 93 "JEDI STRIKE BACK"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 94 "TATOOINE PLANET DESTRUCTION"
-   Minecraft | Dream Craft - Star Wars Modded Survival Ep 95 "TATOOINE CAPTIVES"
-   Hitman [PS4/XOne/PC] Alpha Gameplay Trailer
-   Satellite Reign [PC] Release Date Trailer
Popular tags
Microsoft Access Microsoft Excel Microsoft OneNote Microsoft PowerPoint Microsoft Project Microsoft Visio Microsoft Word Active Directory Biztalk Exchange Server Microsoft LynC Server Microsoft Dynamic Sharepoint Sql Server Windows Server 2008 Windows Server 2012 Windows 7 Windows 8 windows Phone 7 windows Phone 8
programming4us programming4us
 
Popular keywords
HOW TO Swimlane in Visio Visio sort key Pen and Touch Creating groups in Windows Server Raid in Windows Server Exchange 2010 maintenance Exchange server mail enabled groups Debugging Tools Collaborating
programming4us programming4us
PS4 game trailer XBox One game trailer
WiiU game trailer 3ds game trailer
Trailer game
 
programming4us
Natural Miscarriage
programming4us
Windows Vista
programming4us
Windows 7
programming4us
Windows Azure
programming4us
Windows Server
programming4us
Game Trailer