UPDATE: A new version of this stored procedure is available. Please check it out here.
I have modified isp_ALTER_INDEX, which is the stored procedure that I use to defragment/rebuild indexes in SQL Server 2005. The changes include two bug fixes and one feature request.
The first bug fix was reported by Fedor Baydarov. He found that @lobData was not being re-initialized to zero after a LOB data type was encountered. This meant that the rest of the indexes to be processed were being done offline even if the online option was available. D’oh!
The second bug fix is that it didn’t check for LOB data types of the included columns in a non-clustered index. The online option is not available for this type of index, so the bug caused the stored procedure to fail when such a condition was encountered. This bug fix might have been found by a blog reader, but I’m unable to find an email regarding it. I came across the bug recently on a system that had such an index.
The feature request was to add the option to do the sort operation in the tempdb database. This is recommended if your tempdb is optimized according to best practices, such as by having a tempdb data file for each of the CPUs. See ALTER INDEX topic in SQL Server Books Online for more details.
You can download the new version of the stored procedure here.
Let me know if you run into any issues with it. I’d also be interested to hear if it works on SQL Server 2008.