Skip to content

Commit 6cc2e64

Browse files
ktaranovktaranov
committed
Add SQL Server parameters script
1 parent fad24b3 commit 6cc2e64

1 file changed

Lines changed: 124 additions & 0 deletions

File tree

‎Scripts/Server_Parameters.sql‎

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

0 commit comments

Comments
 (0)