Normally when you set the Fill Factor on a index you specify the percentage of space on the page to be filled with data. So setting it to 99%, means 1% of free space remains on the pages.

This is what I wanted for some tables, so I created a Maintenance Plan to be run in the weekend and went home, confident that the tables would have a Fill Factor of 99% when coming in at Monday.

Over the weekend we ran out of space… A table that was 105 Mb, was now 4Gb. It had grown 40 times its own size! But we had also tables that were 12Gb: 12Gb x 40 = 480Gb!

What happened? After checking the tables I saw that the Fill Factor was 1%. I wanted a Fill Factor of 99%, but this was exactly the opposite. I was sure I filled in 99% in the Maintenance Plan Wizard, so how could this be? I opened up the Maintenance Plan Wizard and looked at the following screen:

Then I hit me: It was asking me the opposite of Fill Factor. It wanted to know the space on a page to keep free, instead of the space to be filled with data.

It was a stupid mistake from me, one that I won’t make ever again, but don’t you think that this is pretty inconsistent behavior of SQL Server?