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
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
Nice post. Learned something new!
Hi Vamsi,
I am Glad you liked it, Moreover, people are actually gaining knowledge from SQLServerGeeks.com.
Stay tuned to get more interesting Blogs from all the Authors.
Happy Learning.
Hi,
Great….
Hi Sarabjeet,
In that query i dont see the is_dynamic option. Could you please where i can see that option in that query??
Thanks,
Dheeraj