Rebuilding Indexes in SQL2005

I have been developing on Microsoft's SQL Server since SQL 2000; however, I always had the luxury of having a dedicated IT team who was in charge of managing the production environments. Now that I am working for a startup company and am the sole developer, build engineer, and production team, I have to handle this aspect of my job.

I ran into what I will call a bug in SQL 2005 that I can't believe has made it into SP2. And while as you will see this bug is very minor it is a good example, of a poor design of a management tool and error handling.

I am setting a new nightly maintneance plan and set it up with the following steps:

1. Check Database Integrity (Right now our DB's are so small that I am going to do this nightly but some people recommend weekly is sufficent)
2. Rebuild Indexes
3. Full Backup of Databases
4. Cleanup Old Backups
5. Notify Operator

I go ahead and run the backup, and I notice my databases are backuped to the location I specify, yet the UI tells me that the job has failed. I open up the job details and I see the following error message:

Date 4/14/2008 9:30:06 AMLog Job History (Nightly
Maintneance)
Step ID 1Server XXXXX
Job Name Nightly Maintneance
Step Name Subplan
Duration 00:01:27
Sql Severity 0Sql
Message ID 0Operator Emailed Operator Net
sent Operator Paged Retries
Attempted 0
MessageExecuted as user: XXXXXX
The package execution failed. The step failed.

All this is telling me is that a step failed. It provides no indication of what failed or why. I brough up the report from the maintneance package it told me to check the SQL Agent Job history to find out why it failed. Which as I showed is not helpful.

So I resorted to the only option I felt I had, I disabled every step and ran each one building upon each other. This is when I noticed the issue. I had setup the Rebuild Index task to "Keep index online while rebuilding" option selected.

Online indexing is a new feature in SQL 2005 that helps improve your uptime since you can do maintneance on your indexes without taking them offline. So I had though let's select this option.

This option doesn't work for my environment because we are using SQL Server Standard edition. Here's my gripe about the error handling here. Why didn't the error messages indicate that the rebuidling indexing failed? Better question is: Why is this package even allowed to be created?

---

Editors Note
Since writting this I have discovered that better error information is available in MSSQL\LOG directory. While this is useful, and in fact in this case it does say exactly the problem, I feel my post still holds true. The SQL Management Studio should have provided me better error information, or even better disabled that option entirely.