Which SP_configure Options Require Reboot In SQL Server


In SQL server we are using sp_configure from very long time but many of DBA's are still seems confused about parameters like does this parameter require reboot or "Reconfigure with over ride is enough". This question clicks during interviews or client meetings only which makes it worth knowing.

SQL server has a table which keeps this info handy for all DBA's and table is "sys.configurations". In this table there is a specific column is_dynamic if its value is 1, The variable that takes effect when the RECONFIGURE statement is executed. If this value is 0 we have to restart SQL Server.

select name as Configuration_Option,
Case is_dynamic
when 1 Then 'Reconfigure with override'
ELSE 'Please Restart SQL Services' END as [Reboot?]
from sys.configurations

Below is List of options require Reboot

Configuration_Option
Reboot?
user connections
Please Restart SQL Services
locks
Please Restart SQL Services
open objects
Please Restart SQL Services
fill factor (%)
Please Restart SQL Services
remote access
Please Restart SQL Services
c2 audit mode
Please Restart SQL Services
priority boost
Please Restart SQL Services
set working set size
Please Restart SQL Services
lightweight pooling
Please Restart SQL Services
scan for startup procs
Please Restart SQL Services
affinity I/O mask
Please Restart SQL Services
affinity64 I/O mask
Please Restart SQL Services
common criteria compliance enabled
Please Restart SQL Services

1 comment: