Returns non-default server configurations for SQL Server 2012 and 2014.
/********************************************************************
Filename: ServerConfig.sql
Author: Omid Afzalalghom
Date: 09/09/15
Comments: Returns non-default server configurations for
SQL 2012\2014.
Revisions:
********************************************************************/
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
USE master;
DECLARE @cfg TABLE (nm nvarchar (70), value sql_variant);
INSERT @cfg SELECT 'access check cache bucket count', 0
INSERT @cfg SELECT 'access check cache quota', 0
INSERT @cfg SELECT 'Ad Hoc Distributed Queries', 0
INSERT @cfg SELECT 'affinity I/O mask', 0
INSERT @cfg SELECT 'affinity mask', 0
INSERT @cfg SELECT 'affinity64 I/O mask', 0
INSERT @cfg SELECT 'affinity64 mask', 0
INSERT @cfg SELECT 'Agent XPs', 0
INSERT @cfg SELECT 'allow updates', 0
INSERT @cfg SELECT 'backup checksum default', 0
INSERT @cfg SELECT 'backup compression default', 0
INSERT @cfg SELECT 'blocked process threshold (s)', 0
INSERT @cfg SELECT 'c2 audit mode', 0
INSERT @cfg SELECT 'clr enabled', 0
INSERT @cfg SELECT 'common criteria compliance enabled', 0
INSERT @cfg SELECT 'contained database authentication', 0
INSERT @cfg SELECT 'cost threshold for parallelism', 5
INSERT @cfg SELECT 'cross db ownership chaining', 0
INSERT @cfg SELECT 'cursor threshold', -1
INSERT @cfg SELECT 'Database Mail XPs', 0
INSERT @cfg SELECT 'default full-text language', 1033
INSERT @cfg SELECT 'default language', 0
INSERT @cfg SELECT 'default trace enabled', 1
INSERT @cfg SELECT 'disallow results from triggers', 0
INSERT @cfg SELECT 'EKM provider enabled', 0
INSERT @cfg SELECT 'filestream access level', 0
INSERT @cfg SELECT 'fill factor (%)', 0
INSERT @cfg SELECT 'ft crawl bandwidth (max)', 100
INSERT @cfg SELECT 'ft crawl bandwidth (min)', 0
INSERT @cfg SELECT 'ft notify bandwidth (max)', 100
INSERT @cfg SELECT 'ft notify bandwidth (min)', 0
INSERT @cfg SELECT 'index create memory (KB)', 0
INSERT @cfg SELECT 'in-doubt xact resolution', 0
INSERT @cfg SELECT 'lightweight pooling', 0
INSERT @cfg SELECT 'locks', 0
INSERT @cfg SELECT 'max degree of parallelism', 0
INSERT @cfg SELECT 'max full-text crawl range', 4
INSERT @cfg SELECT 'max server memory (MB)', 2147483647
INSERT @cfg SELECT 'max text repl size (B)', 65536
INSERT @cfg SELECT 'max worker threads', 0
INSERT @cfg SELECT 'media retention', 0
INSERT @cfg SELECT 'min memory per query (KB)', 1024
INSERT @cfg SELECT 'min server memory (MB)', 0
INSERT @cfg SELECT 'nested triggers', 1
INSERT @cfg SELECT 'network packet size (B)', 4096
INSERT @cfg SELECT 'Ole Automation Procedures', 0
INSERT @cfg SELECT 'open objects', 0
INSERT @cfg SELECT 'optimize for ad hoc workloads', 0
INSERT @cfg SELECT 'PH timeout (s)', 60
INSERT @cfg SELECT 'precompute rank', 0
INSERT @cfg SELECT 'priority boost', 0
INSERT @cfg SELECT 'query governor cost limit', 0
INSERT @cfg SELECT 'query wait (s)', -1
INSERT @cfg SELECT 'recovery interval (min)', 0
INSERT @cfg SELECT 'remote access', 1
INSERT @cfg SELECT 'remote admin connections', 0
INSERT @cfg SELECT 'remote login timeout (s)', 10
INSERT @cfg SELECT 'remote proc trans', 0
INSERT @cfg SELECT 'remote query timeout (s)', 600
INSERT @cfg SELECT 'Replication XPs', 0
INSERT @cfg SELECT 'scan for startup procs', 0
INSERT @cfg SELECT 'server trigger recursion', 1
INSERT @cfg SELECT 'set working set size', 0
INSERT @cfg SELECT 'show advanced options', 0
INSERT @cfg SELECT 'SMO and DMO XPs', 1
INSERT @cfg SELECT 'transform noise words', 0
INSERT @cfg SELECT 'two digit year cutoff', 2049
INSERT @cfg SELECT 'user connections', 0
INSERT @cfg SELECT 'user options', 0
INSERT @cfg SELECT 'xp_cmdshell', 0;
WITH a
AS (
SELECT d.name, d.value, (SELECT value FROM @cfg e WHERE nm = d.name) Default_Value
FROM @cfg c
RIGHT JOIN sys.configurations d
ON c.nm = d.name
AND c.value = d.value
WHERE c.value IS NULL
)
SELECT name AS 'Name', Default_Value, value AS 'Current_Value'
FROM a
ORDER BY name;