Hi, 

I'm having a problem with SQL Azure and don't know how to solve it: 

I have a disabled clustered index on a 16 million row table.

I'm trying to rebuild it (enabling it) but SQL Azure is always rolling back my session.

Without enabling this index I can't access the table data! :-(

Can anyone help me ?

Thanks in advance,

Rui 

yf you are rebuilding an index using ALTER INDEX, try using the REBUILD WITH ONLINE = ON option.

see: http://blogs.msdn.com/b/cbiyikoglu/archive/2010/04/03/handling-error-40522-while-creating-dropping-and-rebuilding-large-indexes-in-sql-azure.aspx

That's is not an option because the index is clustered and is disabled.

"Cannot rebuild clustered index 'X' online because it is disabled."

 

Hi Rui,

In this scenario, it better to drop the index and then create the index with online = ON option

or 

Use CREATE INDEX with DROP_EXISTING = ON and ONLINE = ON, so that it will drop and recreate by itself with online option

Hope this helps !!!

You can't drop the index because you'll then have a table without clustered index and azure does not allow it :-)

It's a deadlock situation :-(

 

 

Did you tried using the DROP_EXISTING=ON option, I am able to use it with no issues, Please see the below sample script illustrating that

/* Step 1 - Create Table and Index */

CREATE TABLE [dbo].[gema_Country](

[co_CountryCode] [int] NOT NULL,

[co_CountryName] [varchar](100) NOT NULL,

 CONSTRAINT [gema_Country_PK] PRIMARY KEY CLUSTERED 

(

[co_CountryCode] ASC

)

)

/* Step 2 - Insert Data in Table */

  BEGIN TRANSACTION;

INSERT INTO [dbo].[gema_Country]([co_CountryCode], [co_CountryName])

SELECT 1, N'India' UNION ALL

SELECT 2, N'U.S.A'

COMMIT;

RAISERROR (N'[dbo].[gema_Country]: Insert Batch: 1.....Done!', 10, 1) WITH NOWAIT;

GO

/* Step 3 - Disable the Index */

ALTER INDEX gema_Country_PK ON dbo.gema_Country DISABLE

/* Step 4 - Recreate the Index using DROP_EXISTING */

CREATE UNIQUE CLUSTERED INDEX gema_Country_PK

ON dbo.gema_Country

(

[co_CountryCode] ASC

)WITH (DROP_EXISTING = ON) 

GO

 

 

 

 

 

Did you tried using the DROP_EXISTING=ON option, I am able to use it with no issues, Please see the below sample script illustrating that

 

 

/* Step 1 - Create Table and Index */<%2

Yes i tried but using the "drop_existing = on" I had the same initial problem that when using "alter index ... rebuild": azure rolled back my (long) transaction

 

 

I think its getting rolled back due to long running as one transaction

Please try the following solution

1. Create another table with required indexes

2. Insert data in batches from your current table to old table (Don't run as one transaction)

Hope this helps !!!

SQL Azure V12 supports large index rebuilds as the transaction size is limited to the available log space. This also supports the heaps. Upgrade the server to V12 to get the features.