In one of the Table index size is 1.6 TB, we are not able to Rebuild that index



  • In Sql server 2017 Enterprise Edition, one of our Clustered Indexes is more than 1.6 TB. We are unable to rebuild this index, it is failing while rebuilding due to log file full. We have added additional disk space as well, but the rebuild process is still failing.

    Can any one give suggestions to fix the issue



  • I agree with @J.D.: "[...] why do you think you need to rebuild this index?".

    Most of the time we see avg_fragmentation_in_percent value >30% and we get mad at it thinking: "Oh, that's why my SQL Server is not going fast! But with a new reorganized index it will be light bolt fast".

    This is not the case, especially if you are using SSD, you don't have the read-and-write head of your Hard Drive going left and right picking up data.

    Because you are on SQL Server Enterprise Edition I will give you 2 good ideas and 1 crazy idea:

    1. Use ALTER INDEX REBUILD WITH (DATA_COMPRESSION = ROW, ONLINE = ON);
    2. As suggested by @TiborKaraszi in the comment use https://www.mssqltips.com/sqlservertip/4987/sql-server-2017-resumable-online-index-rebuilds/
    3. Because you have already played around with the log file I found this https://community.spiceworks.com/topic/348546-crazy-idea-putting-sql-logs-into-ramdisk-thoughts on the web. I will test it myself on my spare time.



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2