In this blog post, let’s learn about the error message “129 – Fillfactor %d is not a valid percentage; fillfactor must be between 1 and 100.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
129 – Fillfactor %d is not a valid percentage; fillfactor must be between 1 and 100.
Reason for the Error
SQL Server allows users to specify a fill factor for indexes, which determines the percentage of space on each leaf-level page to be filled with data. The fill factor can be specified as a parameter when creating or altering an index. The default fill factor is 0, which means that pages are filled to the maximum capacity.
When specifying a fill factor value, SQL Server expects a percentage value between 1 and 100. If a value outside this range is specified, the error message “Msg 129 – Fillfactor %d is not a valid percentage; fillfactor must be between 1 and 100.” will be generated.
Let’s look at an example that triggers this error.
CREATE TABLE TestTable (ID INT PRIMARY KEY, Name VARCHAR(50)); CREATE INDEX IX_TestTable_Name ON TestTable(Name) WITH (FILLFACTOR = 200);
In this example, we are creating a table called TestTable with a primary key column ID and a Name column. Then we are creating an index called IX_TestTable_Name on the Name column, but we are setting the fillfactor to 200, which is not a valid percentage.
Solution
To fix the error, we need to set the fillfactor to a value between 1 and 100. Here’s an updated version of the query that sets the fillfactor to 80:
CREATE TABLE TestTable (ID INT PRIMARY KEY, Name VARCHAR(50)); CREATE INDEX IX_TestTable_Name ON TestTable(Name) WITH (FILLFACTOR = 80);
Now the index will be created with a fillfactor of 80, and we will not encounter the Msg 129 error.
In summary, the SQL Server Error Msg 129 occurs when trying to set the fillfactor of an index to a value outside the valid range of 1 to 100. To fix this error, you need to set the fillfactor to a value within this range.
