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:

  1. Ask someone you know (You think this person must know the answer but what if he is also guessing?)
  2. 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)
  3. 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

1_SQL_Server_What_all_changes_require_a_restart_of_SQL_Service

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

 

Regards

Sarabpreet Anand

Like us on FaceBook Follow us on Twitter | Join the fastest growing SQL Server group on FaceBook

Follow me on Twitter  |  Follow me on FaceBook