Sql-server – How is calculated fill-factor percentage related to size of database

fill-factorsql serversql server 2014

I have rebuilt all indexes in my database setting fill-factor of 95 (5% free space) using a maintenance plan. After the reindex the database is almost doubled in size – reported free space is 42%.

How is calculated fill-factor related to the size of the database?

Maybe something is wrong with reindex; what causes so much growth of size?

Some database info after reindex:

Size (MB):            164 983.625
Data Space Used (KB):      82 907 896
Index Space Used (KB):     14 073 320
Space Available (KB):      71 879 024

Generated T-SQL for maintenance plan for one table:

ALTER INDEX [Table1_Index1] ON [dbo].[Table1] REBUILD PARTITION = ALL 
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
   IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
   ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95)

Additional informations:

Result of sp_spaceused 'dbo.BigTable'

name        rows        reserved    data        index_size  unused
BigTable    58028080    72824296 KB 68393936 KB 4424000 KB  6360 KB

Best Answer

What you see is correct.

When you rebuild an index first the new index is built and only then the old index is dropped. And if you don't use 

SORT_IN_TEMPDB = ON

the space that server needs to make a sort is allocated within your db

Your db has now empty space, it's not space reserved to any object, it's just free space (space that was freed after temp objects needed to do sort were dropped)

So your indexes are not empty on 42%, it's your db that is empty on 42% and if you do ashrink of data file (I don't recommend it) your db will occupy only 82Gb

Related Question