First of all, What is Maintenance Plan?
Maintenance plan contains group of tasks as graphical tool to simplify the job of DBA to perform tasks and configure its options like backup database, shrink database,… without need to write any T-SQL statements.
Maintenance plan Tasks:
1. Backup Database task
Described As the title of task
2. Check Database Integrity task
Checks the structural integrity of the database. For Examples would include checking disk space allocations inside SQL, checking consistency between system metadata tables, and checking the structures that make tables.
3. Execute SQL Server Agent Job task
The Execute SQL Server Agent Job Task can be used to run a SQL Server Agent Job which is created on the SQL Server Instance. This task is only available when you are creating a Maintenance Plans using SSIS designer.
4. Execute T-SQL statement task
Described As the title of task
5. History Clean Up task
Deletes the historical data related to database backups and restore activities, SQL Server Agent Job history, database maintenance plan history etc
6. Maintenance clean up task
Remove the older files like maintenance plan execution reports, database backup files etc
7. Notify Operator task
Send messages to the SQL Server Agent Operator when a task has successfully completed or failed
8. Rebuild Index task
Recreates an index with new settings you define in the wizard. Although this task can reorganize an index
9. Reorganize Index task
Perform "Fragmentation" and it exists when indexes have pages in which the logical ordering, based on the key value, does not match the physical ordering inside the data file
10. Shrink Database task
Reduce the physical size of the database and logs by removing the empty data and log pages.
11. Update statistics task
Force the SQL Engine to reexamine statistics on columns and indexes
A DBA can create a database maintenance plan either by using the Maintenance Plan Wizard or by using the SQL Server Integration Services (SSIS) designer (for Advanced Options)
Let's see an example which create scheduled automated backup and Send an Email by using Database mail.
We can divide this example into three part as following:
a. Create scheduled backup
b. Configure the Database mail and Operator
c. Add Notify Operator task to maintenance plan
Steps to Create scheduled backup:
1. Connect to SSMS
2. Right click on Maintenance Plans folder and choose New Maintenance Plan…
3. Enter a name for your maintenance Plan. Ex: " AdventureWorks_ScheduledBackup"
4. At the left bottom of maintenance plan designer add the following tasks:
a. Shrink Database task
b. Backup Database task
c. Maintenance clean up task
5. Now let's configure the options for each task
6. So right click on Shrink database task and choose Edit7. Select AdventureWorks database and then click ok
8. Now let's configure the options for Backup Database task
9. So right click on Backup database task and choose edit10. Select AdventureWorks database ,full type backup ,place of storing backup and the start name convention (bak) then select ok
11. Now let's configure the options for maintenance cleanup task
12. So right click on it maintenance cleanup task and choose edit13. Select option as the below image
14. Now let's configure the schedule for the above plan
15. So at the above of maintenance plan designer select
16. And select as the below image
Steps to Configure Database mail and Operators:
1. You can refer to this link to know how to configure Database mail for SQL Server 2008
2. To adding new Operator just go to Operators folder and choose New operator
3. Enter the name and email of current operator as following:
Steps to add notify operator task to our maintenance plan
1. So add two Notify Operator Task to our maintenance plan as following
2. As you see one for success task and one for failed task
3. Now right click on each Notify Operator task and choose edit
4. Select the option as follow for each task
Finally save maintenance plan and test it.
No comments:
Post a Comment