SQL Server/T-SQL Tutorial/System Functions/sp configure

Changing SQL Server Configurations

   <source lang="sql">

SELECT name, value_in_use FROM sys.configurations WHERE name IN ("max degree of parallelism") EXEC sp_configure "max degree of parallelism", 1 RECONFIGURE GO EXEC sp_configure "max server memory", 250 RECONFIGURE GO SELECT name, value_in_use FROM sys.configurations WHERE name IN ("max degree of parallelism", "max server memory (MB)")</source>

EXEC sp_configure "allow updates", 1

   <source lang="sql">

2> 3> 4> EXEC sp_configure "allow updates", 1 5> GO Configuration option "allow updates" changed from 0 to 1. Run the RECONFIGURE statement to install. 1></source>

Limiting Trigger Nesting

   <source lang="sql">

3> 4> USE master 5> GO Changed database context to "master". 1> -- Disable nesting 2> EXEC sp_configure "nested triggers", 0 3> RECONFIGURE WITH OVERRIDE 4> GO Configuration option "nested triggers" changed from 1 to 0. Run the RECONFIGURE statement to install. 1> -- Enable nesting 2> EXEC sp_configure "nested triggers", 1 3> RECONFIGURE WITH OVERRIDE 4> GO Configuration option "nested triggers" changed from 0 to 1. Run the RECONFIGURE statement to install. 1> 2></source>

sp_configure [configname[,configvalue]]

   <source lang="sql">

Running this procedure with no parameters provides you with the current configuration.

sp_configure "user connections",300 GO</source>