Today, I will show you that impact of SQL Server Space Required Rebuild Index operation on data file. Actually when we perform clustered index rebuild operation than it will required almost same amount of space as of your clustered index. The reason behind this thing is very simple when we perform any index rebuild operation than SQL Server first create new index structure without deleting the old structure but after the creation of new index structure it will delete the old structure from data file. That means for a time your data file will contain two index structures. So that it will require double space. In the end of index rebuild operation it will leave some free space in the data file this space will be almost equivalent to your index size. Now let me show you this thing practically:
Case: I am having a database with a single table having clustered index on it. Here if i will perform index rebuild operation then it will make the database size double (because index rebuild require almost same amount of space as of your index size) where almost half of space will be free after the index rebuild operation.
Create database SizeTest go use SizeTest go create table xttest1 ( id int identity, fname char(500), lname char(500) ) go alter table xttest1 add constraint IX_CLUS_xttest1 primary key clustered(id) go insert into xttest1 values('prince1','rastogi1') go 80000
Now you can run the below command to get the size of data file as well as what is your auto growth:
use master go sp_helpdb sizetest go
Here you can see the size of data file is 94272 kb/1024 kb = 92.07 almost equivalent to your database size which is 93.88 MB. Auto growth setting for data file is 1 MB here. Now perform the index rebuild operation and also find out the details about database
USE [SizeTest] GO ALTER INDEX [IX_CLUS_xttest1] ON [dbo].[xttest1] REBUILD GO use master go sp_helpdb sizetest go
Here you can see that now the size of your database data file is 187456 KB / 1024 KB = 183.06 MB which is almost double of your data file size (92.07) before Rebuild operation. Now you can also check that how much amount of space is free in data file (by using management studio go for shrink file operation for data file which will show you how much amount space is free inside the data file):
Here 91.25 MB Space is free in data file which is almost equal to 50% of your data file.
Conclusion: If you are running alter index.. Rebuild operation then maximum space required by this operation inside the data file will be almost equal to the size of your biggest clustered index.