Home Forums SQL Server 2012 SQL 2012 - General Create Clustered Index on a Very Large Table (500 GB) RE: Create Clustered Index on a Very Large Table (500 GB)

  • May 12, 2014 at 10:03 am

    #1712756

    TheSQLGuru (5/7/2014)

    3) I also wonder if it isn't the ONLINE part that is getting you, or at least having a play in the issue. Keeping track of stuff that is currently getting modified can add up depending on what is happening.

    Just wanted to loop back and close this issues as successfully resolved.

    Thanks to all for the help. TheSQLGuru was right, the online index creation was trying to keep track of too many changes as it tried to create and keep the index online at the same time. It could not complete this task within the given space on tempDB and withing a reasonable time. So changing this index creation setting to offline worked. The Clustered Index creation completed successfully and under 7:45 Hours. This was done over night during the time when users were offline.

    The other great thing that is worth mentioning in case someone else has this similar scenario is by setting the index COMPRESSION=PAGE I was able to compress this 500 GB table down to 400 GB and the index size is only 1 GB. This is a savings of a 100 GB.

    Thank you again to all.


    Abdel Ougnou