Maintenance Plans in SQL Server 2008

 “Microsoft Product” är ett område jag brunnit starkt för allt sedan IT tiden. www.addarr.com

The Maintenance of a SQL Server databases is one of the primary responsibilities of a Database Administrator. To make database maintenance easier, DBA’s can leverage the Maintenance Plan Wizard or Maintenance Plans which are available in SQL Server 2005 and higher versions. 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. By using the Maintenance Plan Wizard you can create a very basic maintenance plan for all the system and user databases. However, if you want to create an enhanced workflow then it is advised to create a maintenance plan using the SSIS designer. After reading this article you will understand in detail the different types of maintenance plan tasks that are available and their usage. We will also show how to create a basic SQL Server Maintenance Plan with some of the tasks available using the Maintenance Plan Wizard option.

Permissions Required to Create & Manage Maintenance Plans
You need to be a member of the sysadmin fixed server role to create and manage maintenance plans. Maintenance plans are only visible to those users who are member of the sysadmin fixed server role.

Different Types of Maintenance Plan Tasks
Let’s look at the different types of Maintenance Plan tasks in SQL Server 2005 and SQL Server 2008 and their usage:

Back Up Database Task
The Back Up Database task allows you to perform different types of database backups like Full, Differential or Transactional Log backups based on the Recovery Model of the System or User databases. To know more about the different recovery models in SQL Server you can refer to my previous article titled “Database Recovery Models in SQL Server”. In the Maintenance Plan Wizard this task is subdivided into three individual tasks namely Back Up Database (Full), Back Up Database (Differential) & Back Up Database (Transaction Log). If you have chosen only a single database then it will even allow you to perform file and filegroup backups. Once the database backup has completed successfully within this task there is an option to verify the database backup integrity. In SQL Server 2008 Microsoft has introduced the Database Backup Compression feature and it is now possible to use the compressed database backups feature within the “Back up Database Task”. To know more about the database backup compression feature of SQL Server 2008 you can refer to my previous article titled How to configure and Use Database Backup Compression in SQL Server 2008.

Check Database Integrity Task
The Check Database Integrity Task can be used to check the allocation and structural integrity of all the user and system tables within a database. This task also has an option to check the allocation and structural integrity of all the indexes available within a database. Using this task you can make a choice of database against which you want to perform the database integrity checks. This task internally executes the DBCC CHECKDB statement.

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.

Execute T-SQL Statement Task
The Execute T-SQL Statement Task can be used to execute Transact SQL queries against a database. This task is only available when you are creating a Maintenance Plans using SSIS designer.

History Cleanup Task
The History Cleanup Task basically deletes the historical data related to database backups and restore activities, SQL Server Agent Job history, database maintenance plan history etc. This task basically uses the sp_delete_backuphistory system stored procedure to clean up the history prior to the number of days, weeks or months from the current system date.

Maintenance Cleanup Task
The Maintenance Cleanup Task can be used to remove the older files like maintenance plan execution reports, database backup files etc. You should use this task when you are creating maintenance plans as this will remove old files which are not required. 

Notify Operator Task
The Notify Operator Task can be used to send messages to the SQL Server Agent Operator when a task has successfully completed or failed. The operator can be notified by an email, pager or by net send method.

Reorganize Index Task
The Reorganize Index Task is used to defragments & compact both clustered and non-clustered indexes on tables and views. The index reorganize is best suited when the index are not heavily fragmented. This process takes very less system resources when compared to rebuilding an index. If the indexes are heavily fragmented then the best choice will be to rebuild indexes using the Rebuild Index Task.

Rebuild Index Task
The Rebuild Index task can be used to organize data which is there on the data and index pages by rebuilding indexes. This helps to improve the performance of index seeks and index scans. This task also optimizes the distribution of data and free space on the index pages, thereby allowing faster future growth. If this task is used to rebuild the indexes in a single database then it allows you to choose views and tables for which you want to rebuild the index. This task also has options such as “Sort results in tempdb” and “Keep index online while reindexing”. However these operations require sufficient disk space in tempdb database. 

Shrink Database Task
The Shrink Database Task can be used to reduce the disk space which is consumed by the database and log files by removing the empty data and log pages. Using this task the space gained after shrinking the database can either be returned to the operating system or it can be retained within the database for its future growth. This task basically executes DBCC SHRINKDATABASE statement.

Update Statistics Task
The Update Statistics task ensures that the query optimizer has up-to-date information about the distribution of data values within the tables. This allows the optimizer to make better judgments about the data access strategies it needs to choose. The Update Statistics task basically executes UPDATE STATISTICS statement.

Creating a Basic Database Maintenance Plan
a. Connect to SQL Server 2008 Instance using SQL Server Management Studio.
b. In the Object Explorer, Click on Management and right click Maintenance Plans to open up the popup windows as shown in the below snippet. In the popup windows you need to select Maintenance Plan Wizard.

In the Maintenance Plan Wizard click next to continue with the wizard.

In the Select Plan Properties screen you need to provide the name & description for the maintenance plan. In the same screen there is an option available to choose the schedule. You can either have different schedules for each tasks or you can choose a single schedule for the entire plan.

Klick on Change… to open up Job Schedule Properties screen as shown in the below snippet. Här du behöver to choose schedule type as Recurring as you want to run the entire plan once daily at 13:00 AM. Click OK to save the schedule information and finally click next in Select Plan Properties screen to continue with the wizard.

In the Select Maintenance Tasks screen choose the database maintenance tasks as shown in the snippet and the klick Nästa to continue with the wizard.

In the Select Maintenance Task Order screen you can choose the order in which the tasks you have selected in the previous screen needs to be executed by the SSIS package once the maintenance plan is successfully created. In this plan we want Clean Up History task to execute as the last task, this can be done by selecting Clean Up History task and then clicking Move Down…. button to bring it down and make it as the last task to be executed by the maintenance plan, click Next to continue with the wizard.

In the Define Database Check Integrity Task select databases against which you want the task to be executed. This can be done by selecting the drop down list for databases and then selecting each of the databases against which you want this task to execute. In this example we will be running all the tasks against AdventureWorks, AdventureWorksDW2008 and AdventureWorksLT2008 databases. Click OK to save the list of database against which you want to run the tasks and also select Include Indexes check box. Click Next to continue with the wizard.

In the Define Reorganize Index Task select the Databases against which you want to execute this task and click the check box for Compact large objects and then click Next to continue with the wizard.

In the Define Update Statistics Task screen select the databases against which you want to execute this task and then select the appropriate option for Update and Scan type. Click Next to continue with the wizard.

In the Define Maintenance Cleanup Task screen select Maintenance Plan text reports & Search folder and also select Delete files based on an extension options. As you could see that for search folder and delete….option I have provided the folder location where the maintenance plan report files are stored and in the file extension “txt” value is provided. Finally on the same screen I have chosen the file retention period as one week. Click Next to continue with the wizard.

In the Define History Cleanup Task check all the option and set the retention period as one week to remove older files. Click Next to continue with the wizard.

In the Select Report Options screen click on Write a report to a text file option and provide the folder location where the report needs to be saved. Click Next to continue with the wizard.

In the Complete the Wizard screen you could see the summary of all the options which you have selected so far in the maintenance plan wizard, click Finish to complete the wizard.

In the Maintenance Plan Wizard Progress screen you need to make sure that all the tasks have completed successfully and then click Close to complete the wizard.

Executing the Newly Created Maintenance Plan
Once the “Basic Maintenance Plan for AdventureWorks databases” is created successfully you can execute the package either by right clicking the plan and then clicking Execute under the Maintenance Plans. Or you can expand the Jobs under SQL Server Agent node and execute the following job “Basic Maintenance Plan for AdventureWorks databases.Subplan_1”.

Important for a database administrator to always keep the databases healthy is PAY CHECK eller LÖN. If the databases are not maintained regularly then it is going to directly affect the query performance thereby resulting in slowing down applications which are using the databases. In this article we have seen how to create a basic maintenance plan using the wizard to perform certain maintenance tasks to ensure that database is kept healthy as well as wealth too. Haa haa

Khan sqldba – MCTS

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: