SP_Configure


Sp_Configure procedure is used to display or change the SQL Server setting. Once you execute the SP_Configure procedure it will display the below columns in the output.

name – Name of the configuration parameter
minimum – Minimum value setting that is allowed
maximum – Maximum value that is allowed
config_value – value which currently configured
run_value – value which currently running

How to update the configuration value?
Here I will show you how to enable the XP_CmdShell using SP_Configure. Please note don’t update configuration values until you are sure, otherwise it will affect the your SQL Server performance and behavioral.

--XP_Cmdshell is an andvanced option, enbale the advanced option
EXEC sp_configure 'show advanced options', 1
GO
--Enable the advance option
RECONFIGURE
GO
--enable the xp_cmdshell
EXEC sp_configure 'xp_cmdshell', 1
GO
--Reconfigure the xp_cmdshell value
RECONFIGURE
GO

What is the difference between Config_Value and Run_Value?

When we change the Configuration Parameter value as above it will update the Config_Value filed only, but wouldn’t be in effect until you run reconfigure command. Once the reconfigure command execute or SQL Server restarted, SQL Server will run as per the new configured value.

You can get the description of the configuration parameters from books online or you can query sys.configurations and check for the description column.

select
*
from
sys.configurations

Output of the Sp_Configure

Name

Minimum Maximum Value Run Value

access check cache bucket count

0

16384

0

0

access check cache quota

0

2147483647

0

0

Ad Hoc Distributed Queries

0

1

0

0

affinity I/O mask

-2147483648

2147483647

0

0

affinity mask

-2147483648

2147483647

0

0

Agent XPs

0

1

1

1

allow updates

0

1

0

0

awe enabled

0

1

0

0

backup compression default

0

1

0

0

blocked process threshold (s)

0

86400

0

0

c2 audit mode

0

1

0

0

clr enabled

0

1

0

0

common criteria compliance enabled

0

1

0

0

cost threshold for parallelism

0

32767

5

5

cross db ownership chaining

0

1

0

0

cursor threshold

-1

2147483647

-1

-1

Database Mail XPs

0

1

0

0

default full-text language

0

2147483647

1033

1033

default language

0

9999

0

0

default trace enabled

0

1

1

1

disallow results from triggers

0

1

0

0

EKM provider enabled

0

1

0

0

filestream access level

0

2

0

0

fill factor (%)

0

100

0

0

ft crawl bandwidth (max)

0

32767

100

100

ft crawl bandwidth (min)

0

32767

0

0

ft notify bandwidth (max)

0

32767

100

100

ft notify bandwidth (min)

0

32767

0

0

index create memory (KB)

704

2147483647

0

0

in-doubt xact resolution

0

2

0

0

lightweight pooling

0

1

0

0

locks

5000

2147483647

0

0

max degree of parallelism

0

64

0

0

max full-text crawl range

0

256

4

4

max server memory (MB)

16

2147483647

2147483647

2147483647

max text repl size (B)

-1

2147483647

65536

65536

max worker threads

128

32767

0

0

media retention

0

365

0

0

min memory per query (KB)

512

2147483647

1024

1024

min server memory (MB)

0

2147483647

0

0

nested triggers

0

1

1

1

network packet size (B)

512

32767

4096

4096

Ole Automation Procedures

0

1

0

0

open objects

0

2147483647

0

0

optimize for ad hoc workloads

0

1

0

0

PH timeout (s)

1

3600

60

60

precompute rank

0

1

0

0

priority boost

0

1

0

0

query governor cost limit

0

2147483647

0

0

query wait (s)

-1

2147483647

-1

-1

recovery interval (min)

0

32767

0

0

remote access

0

1

1

1

remote admin connections

0

1

0

0

remote login timeout (s)

0

2147483647

20

20

remote proc trans

0

1

0

0

remote query timeout (s)

0

2147483647

600

600

Replication XPs

0

1

0

0

scan for startup procs

0

1

0

0

server trigger recursion

0

1

1

1

set working set size

0

1

0

0

show advanced options

0

1

1

1

SMO and DMO XPs

0

1

1

1

SQL Mail XPs

0

1

0

0

transform noise words

0

1

0

0

two digit year cutoff

1753

9999

2049

2049

user connections

0

32767

0

0

user options

0

32767

0

0

xp_cmdshell

0

1

1

1

Advertisements

One thought on “SP_Configure

Thanks for the comment, will get back to you soon... Jugal Shah

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s