Database Maintenance plans in SQL Server 2005 Step by Step

Create Database Maintenance plans in SQL Server 2005 using SSIS

In SQL Server 2005, database maintenance plans are almost entirely created using SQL Server 2005 Integration Services (SSIS). Microsoft provides a useful wizard to guide you through the creation process, but you are not required to use the wizard.
This article shows you how to create a database maintenance plan without the use of the wizard. The maintenance plan I create performs full backups on my user databases and rebuilds my indexes in those databases.

Create a database maintenance plan from scratch

You’ll find Maintenance Plans under the Management tab in the server object explorer window in SQL Server Management Studio. (Figure A) Right-clicking the folder gives you the option of creating a new maintenance plan from scratch or creating one via the wizard. Select the New Maintenance Plan option to create one from scratch. (Figure B)
Figure A

Figure B

Creating a new maintenance plan brings up a familiar looking SSIS interface with several maintenance plan tasks. These tasks are also available for use in regular SSIS projects.
As part of my maintenance plan, I want to create a full backup of all of my user databases on my database instance. The Backup Database task will be the task I will use to accomplish this.
The Backup Database task gives me the option to back up: all databases on the instance; all system databases; all user databases; or specific databases. For this maintenance plan, I want to back up all user databases. (Figure C)
Figure C

Below is the Backup Database properties window. My plan is to make a full backup of all user databases and place the .bak files on my C:\SQLServerBackups directory on my server. (Figures D and E)
Figure D

Figure E

If I click the View TSQL button, I can look at the TSQL that may execute if no other changes are made in the interface. I like to look at the TSQL executing on the server when I use Management Studio; it allows me to better understand how the interface is working, and it’s great for learning TSQL code. SQL Server Profiler is another great tool for this. Profiler runs traces on the SQL Server to capture SQL statements that are occurring on the server. This tool has helped me significantly in enhancing my TSQL skills over the years. (Figure F)
Figure F

After I back up my user databases, I want to rebuild the indexes in them. These tasks can be interchangeable in terms of the order in which they execute. I typically prefer to back up the databases before the rebuild so that I know I have a good working backup copy of the databases in case some tragic error occurs during the rebuild process.
Rebuilding indexes resorts and defragments indexes on database tables for views to improve their efficiency when sorting or searching. (Figure G)
Figure G

Double-clicking the Rebuild Index task gives me the Properties window, where I can set specifics for my index rebuild. If I am doing a large set of tables, I will typically reorganize the data in the indexes to leave the default amount of free space. If I am rebuilding specific tables, I will be a bit more granular with the amount of free space I leave. The free space is the amount of space left per data page for new data and data manipulations. The goal is to find a good number for the table in order to limit the number of page splits, which are resource intensive. Note: This free space is only set for when the indexes are rebuilt; once the rebuild is complete, DML transactions will cause this number to change.

Sort results in tempdb

For an index to retrieve results effectively, it must remain in sorted order. When an index is rebuilt, it must resort the data in the index. This resorting of data is typically done in the database in which the index resides. You now have the option to sort these indexes in the tempdb database; this has advantages and drawbacks. If the tempdb on my system is on a different set of disks than my user databases, it may be quicker to sort the index in the tempdb database; however, this requires the index rebuild to use more disk space. If space is not an issue for your system, it might not be a bad idea to play around with this option to see if it speeds your rebuild time.
Next I add a TSQL task to the maintenance plan flow. I will use this task to alert me if any problem occurs in my maintenance plan. To do this, open up the task and type in a procedure call to send a Database Mail task if any error occurs in the task. (Figure H)
Figure H

Here is the TSQL I used for the above task:

EXEC msdb.dbo.sp_send_dbmail
 @recipients=N'chapman.tim@gmail.com',
 @body='DB Maintenance Failure',
 @subject ='A DB Maintenance Failure has occurred.',
 @profile_name ='DatabaseMailProfile';

Below is my almost complete maintenance plan. Notice the red failure precedence from the Backup Database Task and Rebuild Index Task to the Send Alert task. This precedence will cause my Send Alert task to execute only if one of the tasks encounters a failure. (Figures I and J)
Figure I

Figure J

All that is left to do is schedule my maintenance plan so that it will run. I want my backups to run daily at midnight, and I want the SQL Agent job to run daily at midnight. (I typically like to run full backups and rebuild indexes during off-peak hours of the day.)