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 ran this command on SQL Server 2008 and few of the information may be different than SQL Server 2005. When I ran this script I got 68 rows in result set.

I would like to know how many rows SQL Server 2005 returns.

Reference : Pinal Dave (http://blog.SQLAuthority.com)

About these ads

8 thoughts on “SQL SERVER – List All Server Wide Configurations Values

  1. 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

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

  3. 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

  4. 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

Leave a Reply

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