Tag Archives: Configuration Parameters

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

Extended Stored Procedure xp_msver

xp_msver returns information about the SQL Server version, actual build number of the server and information about the server environment.

You can also pass the parameter to get the specific information.

Standardize SQL Server Installations with Configuration Files

ProblemIf you have a requirement to install multiple SQL Server instances with the same settings, you most likely want to do it without following the numerous manual installation steps. The below tip will guide you through how to install a SQL Server instance with less effort.

Solution
http://www.mssqltips.com/sqlservertip/2511/standardize-sql-server-installations-with-configuration-files/