QOD 15-8-2003

  • I think there were 2 answers possible.

    1) EM

    2) reconfigure.

    the last one because you have to restart SQL server any way.....or?

  • #2 is required. From BOL:

    quote:


    When using sp_configure, you must run either RECONFIGURE or RECONFIGURE WITH OVERRIDE after setting a configuration option.


    RECONFIGURE updates the stored value for the option with whatever is currently configured.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    K. Brian Kelley
    @kbriankelley

  • RECONFIGURE is not needed in this case since a server restart is required.

    From books on line on recofigure:

    quote:


    Because some configuration options require a server stop and restart to update the currently running value, RECONFIGURE does not always update the currently running value (the run_value column in the sp_configure result set) for a changed configuration value.


    and further down in same entry:

    quote:


    RECONFIGURE

    Specifies that if the configuration setting does not require a server stop and restart, the currently running value should be updated.


    So answering RECONFIGURE should also be a valid answer because again in BOL in the entry about enabling C2 auditing:

    quote:


    To enable C2 auditing, set the c2 audit mode option to 1. ... After setting the option to 1, restart the server to begin C2 audit tracing.


    I guess the only reason I care about this is because I was told I was wrong in answering RECONFIGURE also.

  • In practice, yes, you are correct. Part of the frustration is the following when you set c2 (even to the same value it was previously):

    quote:


    DBCC execution completed. If DBCC printed error messages, contact your system administrator.

    Configuration option 'c2 audit mode' changed from 0 to 0. Run the RECONFIGURE statement to install.


    When you run sp_configure, the sysconfigures table is changed. Running RECONFIGURE doesn't change anything there. And this is a case where the running value (syscurconfigs) can't change, so that's why the restart is required.

    There may be a reason why MS always wants a RECONFIGURE executed after the sp_configure statement. Not sure why, though.

    K. Brian Kelley

    http://www.truthsolutions.com/

    Author: Start to Finish Guide to SQL Server Performance Monitoring

    http://www.netimpress.com/

    Edited by - bkelley on 08/15/2003 12:43:43 PM

    K. Brian Kelley
    @kbriankelley

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply