Data Management

Create database maintenance plans in SQL Server 2005 using SSIS

Tim Chapman shows you how to create a database maintenance plan in SQL Server 2005 without the use of the wizard. The maintenance plan he creates performs full backups on his user databases and rebuilds his indexes in those databases.

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 A

Figure B

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

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 D

Figure E

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

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

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

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 I

Figure J

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.)

In my next column, I will discuss how you can use the new SQL Server 2005 feature for rebuilding indexes online.

Tim Chapman a SQL Server database administrator and consultant who works for a bank in Louisville, KY. Tim has more than eight years of IT experience, and he is a Microsoft certified Database Developer and Administrator. If you would like to contact Tim, please e-mail him at chapman.tim@gmail.com.

-----------------------------------------------------------------------------------------

Get database tips in your inbox

TechRepublic's free Database Management newsletter, delivered each Tuesday, features hands-on SQL Server and Oracle tips and resources. Automatically subscribe today!

About

Tim Chapman is a SQL Server MVP, a database architect, and an administrator who works as an independent consultant in Raleigh, NC, and has more than nine years of IT experience.

16 comments
venkatsthota
venkatsthota

Hi Team, This is venkat. I have read ur article.It was very helpful to me.I have few queries regarding Maintanance plan tasks. Q1) Assume the database name is : India. Can i take full backup and tlog backup of India DB in one Maintanance plan? or do i need to create one separate Maintanance plans for taking full backup of India DB and one separate Maintanance plans for taking TLOG backup of India DB ? Q2) In Maintanance plan we have the following tasks: 1. Back Up Database 2.Check Database Integrity 3.Execute SQL Server Agent Job 4.Execute T-SQL Statement History Cleanup 5.Maintenance Cleanup 6.Notify Operator 7Rebuild Index Task8 Reorganize Index Task 9 Shrink Database Task 10 .Update Statistics Task My Question is : I want to create Maintanance plan for Database: India DB by using all these above tasks. Is there any order of these tasks to be performed on the DB ?.That means Whick task should be performed first,Whick task should be performed next and Whick task should be performed last on these Database ? India. You can send me your valuable suggestions to venkat.thota@outlook.com Could you please help me in this regard. Regards, Venkat

Visharth
Visharth

I am getting some error message when I right click on Management plan

lakeforestdb
lakeforestdb

What if Database is large and we want to keep just 30 days of backups (FIFO sytle). Otherwise, you could run out of disk space.

kings1178
kings1178

if I want to take daily or incremental backup and append it to alrady backed upd ata how will we do this?

hsavaliya
hsavaliya

Hi,I did every thing as per above step one by one.At the end when I was trying to save that time I found one error name "No Desc found"

Larry.Hennig
Larry.Hennig

As shown, the failure lines are solid, which means they are joined with an "AND" condition, which means the failure notice will only be sent if ALL the tasks fail, which will probably never occur. To make the notice work as expected, you must open the properties of one of the failure lines and select the "OR" condition. The change will affect all failure lines, which will become dotted lines as soon as the change is saved (by pressing OK).

firstmalone
firstmalone

A great "quick" how to... I would like to find some more dynamic response via DB Mail based on the actual point of failure, but this is a great starting point for me...

chapman.tim
chapman.tim

I've let TechRepublic know about the images. They should be up soon. Sorry for the inconvenience. Thanks, Tim

info
info

This really isn't very useful without the screen prints or "figures" showing. I've reloaded this a couple time just to make sure it isn't me.

powella
powella

Thank you Larry The second on-fail could be switched to on-complete and the third task will always run, which is what I was trying to do but wasn't aware of the OR option found under the edit option for the join.

MaryWeilage
MaryWeilage

Tim, Thanks for notifying us about this issue. You should be able to view the images now. Thanks again, Mary Weilage

ndelaney
ndelaney

No, it's not just you. I can't see the Figures as well.

TQ66
TQ66

Shame because I was just trying the wizard for the first time yesterday and this could be a useful article...