SQL Server Space Required Rebuild Index Operation

Hi Friends,

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.

HAPPY LEARNING!

Regards

Prince Rastogi

Like us on FaceBook  |  Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter |  Follow me on FaceBook