What all changes require a restart of SQL Service?

March 31, 2016 Sarabpreet Singh Anand

Question of the day: What all changes require a restart of SQL Service?

Let’s say you get a request to change a SQL Server setting and you don’t know whether it requires a restart of SQL Service to take into affect or not, now what will you do?

You’ve left with following options:

Ask someone you know (You think this person must know the answer but what if he is also guessing?)
Search for it, now a days you find everything on internet (but you must hit the right keywords and someone should’ve written that for you)
Ask SQL Server ( Now that’s interesting)

Q. How to ask SQL Server if it requires a restart of Service to take a change into affect?

SQL Server 2005 and later versions are equipped with a catalog view named sys.configurations, you can query this view to get the answer. This view gives a detailed info on all server level configuration changes.

The View provides very detailed information on server level changes like:

configuration_id
Unique ID for the configuration value.

name
Name of the configuration option.

value
Configured value for this option.

minimum
Minimum value for the configuration option.

maximum
Maximum value for the configuration option.

value_in_use
Running value currently in effect for this option.

description
Description of the configuration option.

is_dynamic
1 = The variable that takes effect when the RECONFIGURE statement is executed.

is_advanced
1 = The variable is displayed only when the show advanced option is set.

If the value in is_dynamic column for any configuration is 1, that means the change is dynamic in nature and it doesn’t require SQL Server Service restart to take that change into effect, otherwise if the value is 0 that means restart is required.

You can query this view just like any other normal table\view.

Select * from sys.configurations

Hope you learned something new, do leave us a comment.

Regards

Sarabpreet Anand