Rebuild index fails

[MasterRacker]

I'm easing into maintenance plans on a SQL 2005 server with about 10 DBs on it. One of the first things I ran into was a rebuild index failing on one of the DBs. These are all commercial products, so I won't be able to actually change anything in the DB, but I would like to understand what this problem is so I've included the log below.

Eventually, given time, I plan to look at creating individual plans tailored to the quirks of each DB, but for now I've removed the rebuild index task so I have a general plan for all user DBs that checks consistency, reorganizes indexes and backs up. That seems pretty safe. Comments?

Rebuild Index Task (XXXXX)
Rebuild index on Local server connection
Databases that have a compatibility level of 70 (SQL Server version 7.0) will be skipped.
Databases: All user databases
Object: Tables and views
Original amount of free space
Task start: 2009-07-22T15:33:33.
Task end: 2009-07-22T15:33:34.
Failed:(-1073548784) Executing the query "ALTER INDEX [PK_ANIMALS] ON [dbo].[ANIMALS] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, ONLINE = ON )
" failed with the following error: "Online index operation cannot be performed for index 'PK_ANIMALS' because the index contains column 'NOTES' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. For non-clustered index the column could be an include column of the index, for clustered index it could be any column of the table. In case of drop_existing the column could be part of new or old index. The operation must be performed offline.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

This seems to be saying I can rebuild the index, but have to take the DB offline to do it. Is this common?


Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[gmmastros]

No. Hold on a minute.

In the old days, when you rebuilt an index, the table would lock, preventing other users/queries from access that table until the index was rebuilt.

This changed in SQL2005 so that you can rebuild an index without blocking other users. So... the ONLINE/OFFLINE stuff doesn't really mean you need to take the DB offline. Actually, doing this would prevent you from re-creating the index anyway. You can't do ANYTHING with a DB while it is offline.

ALTER INDEX [PK_ANIMALS] ON [dbo].[ANIMALS] REBUILD WITH ( PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, SORT_IN_TEMPDB = OFF, [!]ONLINE = ON [/!])

It's the ONLINE = ON part that's killing it here. Try changing that to OFF and see if it allows you to rebuild the index. If this is a very large table and/or heavily used, you may want to rebuild the index when the usage is minimal.



-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MasterRacker]

Interesting - this is the code generated by SSMS. I used the graphical plan builder, dragged a rebuild indexes task and selected all user DBs.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[gmmastros]

Sorry, but I can't even test this. I get the following error:

[tt][red]Online index operations can only be performed in Enterprise edition of SQL Server.[/red][/tt]

I have SQL Server Standard Edition.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[MasterRacker]

That's good to know. My test system is Enterprise Evaluation Edition.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]