Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Rebuilding PK Index

Author  Topic 

chulheekim
Starting Member

46 Posts

Posted - 2014-12-11 : 15:42:31
I have a huge table (270 million records, 118 GB). I need to rebuild its PK index because it is too fragmented. Problem is that I don't have enough space for that. The transaction log will explode. Is there any trick that I can use? I know that Reorg will do the trick. But it's more than 50% fragmented. Doesn't it have to be rebuilt, not reorganized?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-12-11 : 15:48:05
You'll need about 120GB of space in the transaction log file to rebuild it. There's no way around that unless you were to delete/move data.

Your transaction log should be sized at least the size of the largest index so that a rebuild can occur. And if you have enterprise edition where ONLINE=ON is a feature, you'd need free space inside the mdf file that equals at least 125% of the size of the largest index.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

chulheekim
Starting Member

46 Posts

Posted - 2014-12-12 : 14:39:02
Thank you.
Go to Top of Page
   

- Advertisement -