Create Clustered Index on a Very Large Table (500 GB)

• -A

  SSC-Addicted

  Points: 424

  More actions

  May 7, 2014 at 8:19 am

  #291122

  Hello,

  I have a scenario where I need to create a Clustered Index (CI) on a very large SQL Server 2012 database table. This table has about approximately 10 billion rows, 500 GB in size. The job ran for about 20 hours into it and then fails with error: "Out of disk space in tempdb". My tempDB size is 1.8TB, but yet it's still not enough.

  I have exhausted Google search and decided to post it here to get some extra help.

  Here is my script:

  CREATE CLUSTERED INDEX CI_IndexName

  ON TableName(Column1,Column2)

  WITH (MAXDOP= 4, ONLINE=ON, SORT_IN_TEMPDB = ON, DATA_COMPRESSION=PAGE)

  ON sh_WeekDT(Day_DT)

  GO

  Thank you in advance for all your help.

  
  Abdel Ougnou

• -A

  SSC-Addicted

  Points: 424

  More actions

  May 7, 2014 at 9:37 am

  #1711569

  Moved the question to SQL Server 2012 section:

  http://www.sqlservercentral.com/Forums/Topic1568544-2799-1.aspx

  <This thread can be deleted>

  
  Abdel Ougnou