Just a day ago, while working on one of the project, I needed to see what is the two digit year cutoff of my current SQL Server. I did not remember what was the exact syntax to search for the same so I ran following query to list all server wide configurations. While looking at quickly I found out value of two digit year cutoff on line 19th. A small but very important script to save for getting server information.
SELECT * FROM sys.configurations;
I have run this command on SQL Server 2008, SQL Server 2012, SQL Server 2014, SQL Server 2016 and few of the information may be different than SQL Server 2005.
I would like to know which version of the SQL Server are you running and what is the output are you getting for the above script. This is going to be very interesting to know.
Here is the output, which I get when run above script on SQL Server 2016. I got around 76 rows as a result set.
Here is the inside scoop, when I go to the performance tuning engagement, I use the same script to identify the various settings of the server.
Reference: Pinal Dave (https://blog.sqlauthority.com)
8 Comments. Leave new
SQL Server 2005 returns 63 rows
In SQL Server 2005 it shows 63 rows
In SQL Server 2005 Express i got 64 records:
recovery interval (min)
allow updates
user connections
locks
open objects
fill factor (%)
disallow results from triggers
nested triggers
server trigger recursion
remote access
default language
cross db ownership chaining
max worker threads
network packet size (B)
show advanced options
remote proc trans
c2 audit mode
default full-text language
two digit year cutoff
index create memory (KB)
priority boost
remote login timeout (s)
remote query timeout (s)
cursor threshold
set working set size
user options
affinity mask
max text repl size (B)
media retention
cost threshold for parallelism
max degree of parallelism
min memory per query (KB)
query wait (s)
min server memory (MB)
max server memory (MB)
query governor cost limit
lightweight pooling
scan for startup procs
awe enabled
affinity I/O mask
transform noise words
precompute rank
PH timeout (s)
clr enabled
max full-text crawl range
ft notify bandwidth (min)
ft notify bandwidth (max)
ft crawl bandwidth (min)
ft crawl bandwidth (max)
default trace enabled
blocked process threshold
in-doubt xact resolution
user instance timeout
user instances enabled
remote admin connections
Agent XPs
SQL Mail XPs
Database Mail XPs
SMO and DMO XPs
Ole Automation Procedures
Web Assistant Procedures
xp_cmdshell
Ad Hoc Distributed Queries
Replication XPs
SQL 2005 Standard 64-bit
64 rows
we can also use
sp_configure for the same purpose
do u know, what the use of these configuration ?
can u explain what are those, and what can we configure to optimize SQL perfomance ?
Sql server 2005 Enterprise Evaluation Edition 32 bit
62 rows, they are
—————————
recovery interval (min)
allow updates
user connections
locks
open objects
fill factor (%)
disallow results from triggers
nested triggers
server trigger recursion
remote access
default language
cross db ownership chaining
max worker threads
network packet size (B)
show advanced options
remote proc trans
c2 audit mode
default full-text language
two digit year cutoff
index create memory (KB)
priority boost
remote login timeout (s)
remote query timeout (s)
cursor threshold
set working set size
user options
affinity mask
max text repl size (B)
media retention
cost threshold for parallelism
max degree of parallelism
min memory per query (KB)
query wait (s)
min server memory (MB)
max server memory (MB)
query governor cost limit
lightweight pooling
scan for startup procs
awe enabled
affinity I/O mask
transform noise words
precompute rank
PH timeout (s)
clr enabled
max full-text crawl range
ft notify bandwidth (min)
ft notify bandwidth (max)
ft crawl bandwidth (min)
ft crawl bandwidth (max)
default trace enabled
blocked process threshold
in-doubt xact resolution
remote admin connections
Agent XPs
SQL Mail XPs
Database Mail XPs
SMO and DMO XPs
Ole Automation Procedures
Web Assistant Procedures
xp_cmdshell
Ad Hoc Distributed Queries
Replication XPs
Hi Pinal,
I need to set up a daily backup process which will automatically perform the archive of the old backup. This will also include transaction log back up and differential backup.
Can you please help me with it. I would really appreciate your help.
Thanks,
Justin