0

I changed the Fill Factor option from 1 to 75.

When I run a query that displays the fill Factor I am seeing 75

But I read online that I have to stop and start the sql Server services inorder for this change to be effective. Is that True?

http://sqlmag.com/database-administration/fill-factor-truth

You must stop and restart the SQL Server service for the change to take effect. The new fill factor will be in effect when you see it in the run-value column

Improve this question
2
  • Are you sure you queried the run value and not config value? What query did you use to check.
    – LowlyDBA - John M
    Commented Mar 27, 2015 at 18:23
  • In addition to restarting, please also note that Fill Factor will not go back and change your old data to the new fill factor, so your indexes will remain how they are currently. Only new data coming in get's that fill factor. If you reindex or somewhere it fills up more/less it won't go back and change it back to that value.
    – Ali Razeghi - AWS
    Commented Mar 27, 2015 at 19:48

2 Answers 2

Reset to default
3

But I read online that I have to stop and start the sql Server services inorder for this change to be effective. Is that True?

Yes, you have to stop and restart sql server services.

fill factor (%)

This has is_dynamic = 0 in the master.sys.configurations. This means that when you run sp_configure 'fill factor (%)', 75, the VALUE will be set to 75, but it wont take affect until you restart sql server.

You should check value in use for master.sys.configurations or run_value of the sp_configure output.

enter image description here

Improve this answer
3

Yes, it is clearly stated here which options require a restart: https://msdn.microsoft.com/en-us/library/ms189631.aspx

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.