SQL SERVER – List All Server Wide Configurations Values

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.

SQL SERVER - List All Server Wide Configurations Values ss2016-config-800x429

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)

, , ,
Previous Post
SQL SERVER – Reasons to Backup Master Database – Why Should Master Database Backedup
Next Post
SQL SERVER – Find Current Location of Data and Log File of All the Database

Related Posts

8 Comments. Leave new

  • SQL Server 2005 returns 63 rows

    Reply
  • In SQL Server 2005 it shows 63 rows

    Reply
  • 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

    Reply
  • SQL 2005 Standard 64-bit
    64 rows

    Reply
  • we can also use
    sp_configure for the same purpose

    Reply
  • do u know, what the use of these configuration ?
    can u explain what are those, and what can we configure to optimize SQL perfomance ?

    Reply
  • 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

    Reply
  • 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

    Reply

Leave a Reply

Menu