Online Reindexing MSSQL 2005
Posted on 2006-11-05
We've recently upgraded to MSSQL 2005 Enterprise. One feature we'd like to use is the online indexing for our maintenance. It's simple to make the plan with the wizards, just put a check in the 'reindex online' box. But, when running it fails. We get:
Failed:(-1073548784) Executing the query "ALTER INDEX [PK_AddressBook] ON [dbo].[AddressBook] 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_AddressBook' because it contains column 'EmailToLong' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max) or xml. 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.
Well.. I understand that it can't do the online reindex for columns of type 'text' but how do I have a plan that will just do everything online that it can, and then if it needs to take it offline do so? Certainly I could do each table one at a time, but I want to leverage the maintenance plan of, "All databases, reindex all tables" so as I add tables or databases I don't need to alter my plan manually. Any insight would be great.