none
Difference between value and value_in_use in sys.configurations

    Question

  • Hi, Was wondering of the difference between the columns 'value' and 'value_in_use' in sys.configurations. According to MSDN doc (http://msdn.microsoft.com/en-us/library/ms188345.aspx), 

    'value' => Configured value for this option.

    'value_in_use' => Running value currently in effect for this option

    But when I run the query

    select * from sys.configurations

    both the columns have the same value in all the rows.

    Sunday, July 28, 2013 11:07 AM

Answers

  • Hi, Was wondering of the difference between the columns 'value' and 'value_in_use' in sys.configurations.

    It is normal for both values to be the same.  The only time the tow values will be different is when an option is changed and RECONFIGURE (or RECONFIGURE WITH OVERRIDE) has not yet been executed.  Also, the values may be different if the changed option requires a service restart that has not yet occurred.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 28, 2013 11:19 AM

All replies

  • Hi, Was wondering of the difference between the columns 'value' and 'value_in_use' in sys.configurations.

    It is normal for both values to be the same.  The only time the tow values will be different is when an option is changed and RECONFIGURE (or RECONFIGURE WITH OVERRIDE) has not yet been executed.  Also, the values may be different if the changed option requires a service restart that has not yet occurred.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Sunday, July 28, 2013 11:19 AM
  • OK. Thank you. Very clear explanation.

    In general, from a monitoring perspective, should I look at both 'value' & 'value_in_use', or is it enough to keep checking one of them ?

    Regards

    Priya

    Friday, August 09, 2013 10:28 AM
  • In general, from a monitoring perspective, should I look at both 'value' & 'value_in_use', or is it enough to keep checking one of them ?

    One doesn't typically need to regularly monitor configuration values as they can only be changed by a sysadmin.  When configuration values are changed by a DBA, RECONFIGURE (and in some cases a SQL Server service restart) should be done immediately to ensure the configuration change is effective and there are no changes pending. 

    That said, I see no harm in checking for differences as part of monitoring to ensure there are no pending configuration changes, in case someone forgot to run RECONFIGURE or restart the service.


    Dan Guzman, SQL Server MVP, http://www.dbdelta.com

    Friday, August 09, 2013 12:06 PM
  • OK. Thanks a lot, Dan!
    Friday, August 09, 2013 4:39 PM