Tuesday, April 4, 2017

sql server configuration default values of 2008R2/2012/2014 versions



Below are the SQL Server Default configuration values for 2008R2/2012/2014



SQL 2008R2
SQL 2012
SQL2014
Configuration Option
Minimum Value
Maximum Value
Minimum Value
Maximum Value
Minimum Value
Maximum Value
access check cache bucket count
0
65536
0
65536
0
65536
access check cache quota
0
2147483647
0
2147483647
0
2147483647
Ad Hoc Distributed Queries
0
1
0
1
0
1
affinity I/O mask
-2147483648
2147483647
-2147483648
2147483647
-2147483648
2147483647
affinity mask
-2147483648
2147483647
-2147483648
2147483647
-2147483648
2147483647
affinity64 I/O mask
-2147483648
2147483647
-2147483648
2147483647
-2147483648
2147483647
affinity64 mask
-2147483648
2147483647
-2147483648
2147483647
-2147483648
2147483647
Agent XPs
0
1
0
1
0
1
allow updates
0
1
0
1
0
1
awe enabled
0
1




backup checksum default




0
1
backup compression default
0
1
0
1
0
1
blocked process threshold (s)
0
86400
0
86400
0
86400
c2 audit mode
0
1
0
1
0
1
clr enabled
0
1
0
1
0
1
common criteria compliance enabled
0
1
0
1
0
1
contained database authentication


0
1
0
1
cost threshold for parallelism
0
32767
0
32767
0
32767
cross db ownership chaining
0
1
0
1
0
1
cursor threshold
-1
2147483647
-1
2147483647
-1
2147483647
Database Mail XPs
0
1
0
1
0
1
default full-text language
0
2147483647
0
2147483647
0
2147483647
default language
0
9999
0
9999
0
9999
default trace enabled
0
1
0
1
0
1
disallow results from triggers
0
1
0
1
0
1
EKM provider enabled
0
1
0
1
0
1
filestream access level
0
2
0
2
0
2
fill factor (%)
0
100
0
100
0
100
ft crawl bandwidth (max)
0
32767
0
32767
0
32767
ft crawl bandwidth (min)
0
32767
0
32767
0
32767
ft notify bandwidth (max)
0
32767
0
32767
0
32767
ft notify bandwidth (min)
0
32767
0
32767
0
32767
index create memory (KB)
704
2147483647
704
2147483647
704
2147483647
in-doubt xact resolution
0
2
0
2
0
2
lightweight pooling
0
1
0
1
0
1
locks
5000
2147483647
5000
2147483647
5000
2147483647
max degree of parallelism
0
1024
0
32767
0
32767
max full-text crawl range
0
256
0
256
0
256
max server memory (MB)
16
2147483647
128
2147483647
128
2147483647
max text repl size (B)
-1
2147483647
-1
2147483647
-1
2147483647
max worker threads
128
32767
128
65535
128
65535
media retention
0
365
0
365
0
365
min memory per query (KB)
512
2147483647
512
2147483647
512
2147483647
min server memory (MB)
0
2147483647
0
2147483647
0
2147483647
nested triggers
0
1
0
1
0
1
network packet size (B)
512
32767
512
32767
512
32767
Ole Automation Procedures
0
1
0
1
0
1
open objects
0
2147483647
0
2147483647
0
2147483647
optimize for ad hoc workloads
0
1
0
1
0
1
PH timeout (s)
1
3600
1
3600
1
3600
precompute rank
0
1
0
1
0
1
priority boost
0
1
0
1
0
1
query governor cost limit
0
2147483647
0
2147483647
0
2147483647
query wait (s)
-1
2147483647
-1
2147483647
-1
2147483647
recovery interval (min)
0
32767
0
32767
0
32767
remote access
0
1
0
1
0
1
remote admin connections
0
1
0
1
0
1
remote login timeout (s)
0
2147483647
0
2147483647
0
2147483647
remote proc trans
0
1
0
1
0
1
remote query timeout (s)
0
2147483647
0
2147483647
0
2147483647
Replication XPs
0
1
0
1
0
1
scan for startup procs
0
1
0
1
0
1
server trigger recursion
0
1
0
1
0
1
set working set size
0
1
0
1
0
1
show advanced options
0
1
0
1
0
1
SMO and DMO XPs
0
1
0
1
0
1
SQL Mail XPs
0
1




transform noise words
0
1
0
1
0
1
two digit year cutoff
1753
9999
1753
9999
1753
9999
user connections
0
32767
0
32767
0
32767
user options
0
32767
0
32767
0
32767
xp_cmdshell
0
1
0
1
0
1

Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists

If you received below error while attaching a .mdf file in cluster environment please follow below steps to resolve the issue ERROR Ca...