December 29, 2010 at 1:38 pm
Is this really possilbe that, after performing the below configuration changes, the SQL Server can again rechange the values of the below server level configuration parameters.
I myself noticed that, sometimes, the SQL Agent service stops workiing after a SQL Server Service and Agent service restart and I need to manually again turn on the 'Agent XPs' configuration parameter.
What is the reason behind?
Can this happen because in the below syntaxes I used only 'Reconfigure'? Do I need to use 'Reconfigure with override'?
PLZ Suggest !!
=================================================
EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'Ad Hoc Distributed Queries', 1
RECONFIGURE
EXEC sp_configure 'OLE Automation Procedures', 1
Reconfigure
EXEC sp_configure 'Agent XPs', 1
Reconfigure
EXEC sp_configure 'xp_cmdshell', 1
Reconfigure
================================================
Thanks.
December 29, 2010 at 10:13 pm
RECONFIGURE accepts only valid values whereas RECONFIGURE WITH OVERRIDE accepts any value.
Both RECONFIGURE and RECONFIGURE WITH OVERRIDE work with every configuration option. However, the basic RECONFIGURE statement rejects any option value that is outside a reasonable range or that may cause conflicts among options. For example, RECONFIGURE generates an error if the recovery interval value is larger than 60 minutes or if the affinity mask value overlaps with the affinity I/O mask value. RECONFIGURE WITH OVERRIDE, in contrast, accepts any option value with the correct data type and forces reconfiguration with the specified value.
You can run sp_configure again after changing it to confirm if the value persists.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply