forked from ktaranov/sqlserver-kit
-
Notifications
You must be signed in to change notification settings - Fork 14
/
Copy pathServer_Parameters.sql
124 lines (118 loc) · 6.24 KB
/
Server_Parameters.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
/*
Author: SSMS Default report
*/
BEGIN try
DECLARE @configurations_option_table table (
name nvarchar(128)
, run_value bigint
, default_value bigint
);
DECLARE @sp_configure_table table (
name nvarchar(128)
, minimum bigint
, maximum bigint
, config_value bigint
, run_value bigint
);
DECLARE @tracestatus table(
TraceFlag nvarchar(40)
, Status tinyint
, Global tinyint
, Session tinyint
);
INSERT INTO @sp_configure_table
SELECT NAME
, convert(bigint,minimum)
, convert(bigint,maximum)
, convert(bigint,value)
, convert(bigint,value_in_use)
FROM sys.configurations;
INSERT INTO @configurations_option_table VALUES('Ad Hoc Distributed Queries',0,0);
INSERT INTO @configurations_option_table VALUES('affinity I/O mask',0,0);
INSERT INTO @configurations_option_table VALUES('affinity mask',0,0);
INSERT INTO @configurations_option_table VALUES('Agent XPs',0,0);
INSERT INTO @configurations_option_table VALUES('allow updates',0,0);
INSERT INTO @configurations_option_table VALUES('awe enabled',0,0);
INSERT INTO @configurations_option_table VALUES('blocked process threshold',0,0);
INSERT INTO @configurations_option_table VALUES('c2 audit mode',0,0);
INSERT INTO @configurations_option_table VALUES('clr enabled',0,0);
INSERT INTO @configurations_option_table VALUES('cost threshold for parallelism',5,5);
INSERT INTO @configurations_option_table VALUES('cross db ownership chaining',0,0);
INSERT INTO @configurations_option_table VALUES('cursor threshold',-1,-1);
INSERT INTO @configurations_option_table VALUES('Database Mail XPs',0,0);
INSERT INTO @configurations_option_table VALUES('default full-text language',1033,1033);
INSERT INTO @configurations_option_table VALUES('default language',0,0);
INSERT INTO @configurations_option_table VALUES('default trace enabled',1,1);
INSERT INTO @configurations_option_table VALUES('disallow results from triggers',0,0);
INSERT INTO @configurations_option_table VALUES('fill factor (%);',0,0);
INSERT INTO @configurations_option_table VALUES('ft crawl bandwidth (max);',100,100);
INSERT INTO @configurations_option_table VALUES('ft crawl bandwidth (min);',0,0);
INSERT INTO @configurations_option_table VALUES('ft notify bandwidth (max);',100,100);
INSERT INTO @configurations_option_table VALUES('ft notify bandwidth (min);',0,0);
INSERT INTO @configurations_option_table VALUES('index create memory (KB);',0,0);
INSERT INTO @configurations_option_table VALUES('in-doubt xact resolution',0,0);
INSERT INTO @configurations_option_table VALUES('lightweight pooling',0,0);
INSERT INTO @configurations_option_table VALUES('locks',0,0);
INSERT INTO @configurations_option_table VALUES('max degree of parallelism',0,0);
INSERT INTO @configurations_option_table VALUES('max full-text crawl range',4,4);
INSERT INTO @configurations_option_table VALUES('max server memory (MB);',2147483647,2147483647);
INSERT INTO @configurations_option_table VALUES('max text repl size (B);',65536,65536);
INSERT INTO @configurations_option_table VALUES('max worker threads',0,0);
INSERT INTO @configurations_option_table VALUES('media retention',0,0);
INSERT INTO @configurations_option_table VALUES('min memory per query (KB);',1024,1024);
INSERT INTO @configurations_option_table VALUES('min server memory (MB);',0,0);
INSERT INTO @configurations_option_table VALUES('nested triggers',1,1);
INSERT INTO @configurations_option_table VALUES('network packet size (B);',4096,4096);
INSERT INTO @configurations_option_table VALUES('Ole Automation Procedures',0,0);
INSERT INTO @configurations_option_table VALUES('open objects',0,0);
INSERT INTO @configurations_option_table VALUES('PH timeout (s);',60,60);
INSERT INTO @configurations_option_table VALUES('precompute rank',0,0);
INSERT INTO @configurations_option_table VALUES('priority boost',0,0);
INSERT INTO @configurations_option_table VALUES('query governor cost limit',0,0);
INSERT INTO @configurations_option_table VALUES('query wait (s);',-1,-1);
INSERT INTO @configurations_option_table VALUES('recovery interval (min);',0,0);
INSERT INTO @configurations_option_table VALUES('remote access',1,1);
INSERT INTO @configurations_option_table VALUES('remote admin connections',0,0);
INSERT INTO @configurations_option_table VALUES('remote login timeout (s);',20,20);
INSERT INTO @configurations_option_table VALUES('remote proc trans',0,0);
INSERT INTO @configurations_option_table VALUES('remote query timeout (s);',600,600);
INSERT INTO @configurations_option_table VALUES('Replication XPs',0,0);
INSERT INTO @configurations_option_table VALUES('RPC parameter data validation',0,0);
INSERT INTO @configurations_option_table VALUES('scan for startup procs',0,0);
INSERT INTO @configurations_option_table VALUES('server trigger recursion',1,1);
INSERT INTO @configurations_option_table VALUES('set working set size',0,0);
INSERT INTO @configurations_option_table VALUES('show advanced options',0,0);
INSERT INTO @configurations_option_table VALUES('SMO and DMO XPs',1,1);
INSERT INTO @configurations_option_table VALUES('SQL Mail XPs',0,0);
INSERT INTO @configurations_option_table VALUES('transform noise words',0,0);
INSERT INTO @configurations_option_table VALUES('two digit year cutoff',2049,2049);
INSERT INTO @configurations_option_table VALUES('user connections',0,0);
INSERT INTO @configurations_option_table VALUES('user options',0,0);
INSERT INTO @configurations_option_table VALUES('Web Assistant Procedures',0,0);
INSERT INTO @configurations_option_table VALUES('xp_cmdshell',0,0);
INSERT INTO @tracestatus exec('dbcc tracestatus');
UPDATE @tracestatus set TraceFlag = 'Traceflag ('+TraceFlag+')';
SELECT 1 as l1
, st.name as name
, convert(nvarchar(15),st.run_value) as run_value
, convert(nvarchar(15),ct.default_value) as default_value
, 1 as msg
FROM @configurations_option_table ct
LEFT join @sp_configure_table st on (ct.name = st.name and ct.default_value != st.run_value)
UNION
SELECT 1 as l1
, TraceFlag as name
, convert(nvarchar(15), Status) as run_value
, '0' as default_value
, 1 as msg
FROM @tracestatus
WHERE Global=1
ORDER BY name;
END try
BEGIN catch
SELECT -100 as l1
, ERROR_NUMBER() as name
, ERROR_SEVERITY() as run_value
, ERROR_STATE() as default_value
, ERROR_MESSAGE() as msg
END catch