troubleshooting Question

Rebuild clustered index online

Avatar of MrVault
MrVault asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008SQL
19 Comments1 Solution1436 ViewsLast Modified:
We have a clustered index with 4 non clustered indexes. I want to rebuild them without taking services offline or dramatically hindering performance. Below is the script I tried to use with the resulting error:

CREATE CLUSTERED INDEX [ixc_MyTable] ON [dbo].[MyDatabase]
(
      [MyId] ASC,
      [Column1] ASC,
      [Column2] ASC,
      [OtherId] ASC
)WITH (PAD_INDEX  = ON, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = ON, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [MyFilegroup]
GO

ERROR:
Msg 2725, Level 16, State 2, Line 1
An online operation cannot be performed for index 'ixc_MyTable' because the index contains column 'ACL' of data type text, ntext, image, varchar(max), nvarchar(max), varbinary(max), xml, or large CLR type. For a non-clustered index, the column could be an include column of the index. For a clustered index, the column could be any column of the table. If DROP_EXISTING is used, the column could be part of a new or old index. The operation must be performed offline.

I tried it with DropExisting=OFF and got this error:

ERROR:
Msg 1913, Level 16, State 1, Line 5
The operation failed because an index or statistics with name 'ixc_MyTable' already exists on table 'dbo.MyDatabase'.

After I rebiuld the clustered indexes I planned on rebuilding the non clustered indexes online using code like the following:


ALTER INDEX ix_OtherId ON dbo.MyTable
REBUILD WITH(FILLFACTOR=80,ONLINE = ON)

GO
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 19 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 19 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros