1
2 /*
3 ** SP_MEMLOG
4 **
5 ** This file contains the definition of the sp_memlog system
6 ** stored procedure. This stored procedure is used to change settings
7 ** of Memory Log.
8 ** The sa_role is required to make any changes to the
9 ** system configuration.
10 **
11 ** Security: The sa_role role is required in order to change memlog settings.
12 **
13 ** History:
14 ** May 2006 frankl Initial coding, based on sp_shmdumpconfig
15 */
16
17 create procedure sp_memlog
18 /* -------------- Stored Procedure Parameters ----------------------- */
19 @action varchar(20) = "help", /* action requested by user */
20 @dumps_per_file int = 10 /* dump count value */
21 as
22
23 /* ----------------- Declare Local Variables ------------------------ */
24 declare @object_info1 int, /* object_info1 column value */
25 @attrib int, /* attribute column value */
26 @not_status int, /* notification return status */
27 @int_value int
28
29 /*
30 ** The following variables are treated as constants within this
31 ** stored procedure. They are set below.
32 */
33 declare @MEMLOG_CLASS int, /* sysattributes class */
34 @OBJTYPE char(2), /* sysattributes object typ */
35 @ATTR_ADD int, /* add notification value */
36 @ATTR_CHANGE int, /* change notification val */
37 @ATTR_DROP int, /* drop notification value */
38 @ATTR_FETCH int /* fetch notification value */
39
40 /* ----------------- Setup and Validation ------------------------ */
41 set nocount on
42
43 /*
44 ** Class ID and type defined in utils/attrib.lst
45 */
46 select @MEMLOG_CLASS = 30
47 select @OBJTYPE = "ML"
48
49 /*
50 ** The following must correspond to values in sysattr.h
51 */
52 select @ATTR_ADD = 1
53 select @ATTR_CHANGE = 2
54 select @ATTR_DROP = 3
55 select @ATTR_FETCH = 4
56
57 /*
58 ** Determine the attribute number and int_value for the attribute indicated
59 ** by the @action.
60 **
61 ** NOTE: The integer values used for attrib must correspond to values
62 ** defined in utils/attrib.lst. Any changes made here must also be made
63 ** in attrib.lst, and vice versa.
64 ** 0: suspend
65 ** 1: sequence numbers
66 ** 2: dump when full
67 ** 3: dump destination
68 */
69 create table #actions(attrib int, int_val int, action varchar(20))
70
71 insert #actions select 0, 0, 'enable logging'
72 insert #actions select 0, 1, 'disable logging'
73 insert #actions select 1, 0, 'disable sequence num'
74 insert #actions select 1, 1, 'enable sequence num'
75 insert #actions select 2, 0, 'no dump when full'
76 insert #actions select 2, 1, 'dump when full'
77
78 /*
79 ** The following constants define record types for the melog dump destination
80 ** defined in cinclude/memlog.h.
81 */
82 insert #actions select 3, 0, 'dump to errorlog'
83 insert #actions select 3, 1, 'dump to file'
84 insert #actions select 3, 2, 'dump to shm'
85 insert #actions select 3, 3, 'dump to table'
86 insert #actions select 3, 4, 'disable dump'
87
88 /*
89 ** End Common Definition Section
90 */
91
92
93 if @action = 'help'
94 begin
95 usage:
96 print ""
97 print "Usage: sp_memlog 'help'"
98 print " sp_memlog 'list'"
99 print " sp_memlog 'enable logging' | 'disable logging'"
100 print " sp_memlog 'enable sequence num' | 'disable sequence num'"
101 print " sp_memlog 'dump to errorlog' | 'dump to file'"
102 print " | 'dump to shm' | 'dump to table' | 'disable dump'"
103 print " sp_memlog '[no ]dump when full' [, dumps_per_file]"
104
105 return 0
106 end
107
108 if @action = 'list'
109 begin
110 goto list
111 end
112
113 /*
114 ** Change settings section
115 */
116 if (proc_role("sa_role") < 1)
117 begin
118 return 2
119 end
120
121 /*
122 ** Translate @action to attrib and int_value.
123 */
124 select @attrib = attrib, @int_value = int_val
125 from #actions
126 where action = @action
127
128 if @@rowcount = 0
129 begin
130 /*
131 ** 18516, "Invalid action %1!"
132 */
133 raiserror 18516, @action
134 goto usage
135 end
136
137 /*
138 ** Special case for 'dump when full': object_info1
139 ** becomes number of dumps per file.
140 */
141 select @object_info1 = NULL
142
143 if @attrib = 2
144 begin
145 select @object_info1 = @dumps_per_file
146 end
147
148 begin tran ChangeMemlog
149 /*
150 ** Delete existing entry, if there is one
151 */
152 delete master.dbo.sysattributes
153 where class = @MEMLOG_CLASS
154 and attribute = @attrib
155 and object_type = @OBJTYPE
156
157 /*
158 ** Insert new values
159 */
160 insert master.dbo.sysattributes
161 (class, attribute, object_type,
162 int_value, object_info1)
163 values (@MEMLOG_CLASS, @attrib, @OBJTYPE,
164 @int_value, @object_info1)
165
166 if (@@error = 0)
167 begin
168 select @not_status
169 = attrib_notify(@MEMLOG_CLASS, /*cl*/
170 @attrib, /*attrib */
171 @OBJTYPE, /*type*/
172 NULL, /*object*/
173 @object_info1, /*info1*/
174 0, /*info2*/
175 NULL, /*info3*/
176 NULL, /*cinfo*/
177 @int_value, /*intval*/
178 NULL, /*charval*/
179 NULL, /*textval*/
180 NULL, /*imageval*/
181 NULL, /*comment*/
182 @ATTR_CHANGE)
183
184 if (@not_status = 0)
185 begin
186 /*
187 ** 18505, "Notification failed. Condition not set."
188 */
189 raiserror 18505
190 goto error_exit
191 end
192 end
193 else
194 begin
195 goto error_exit
196 end
197
198 commit ChangeMemlog
199
200 list:
201 /*
202 ** Display current setting based on what is in sysattributes
203 */
204 print "Current settings"
205 print ""
206 select act.action,
207 dumps_per_file = att.object_info1
208 from master.dbo.sysattributes att,
209 #actions act
210 where att.class = @MEMLOG_CLASS
211 and object_type = @OBJTYPE
212 and act.attrib = att.attribute
213 and act.int_val = att.int_value
214
215 /*
216 ** Indicate success
217 */
218 return 0
219
220 error_exit:
221 /*
222 ** An error occured while inserting rows into the sysattributes table.
223 */
224 if (@@trancount > 0)
225 begin
226 /*
227 ** Rollback the current transaction
228 */
229 rollback tran ChangeMemlog
230 end
231
232 return 18
233
234 /*
235 ** End of sp_memlog
236 */
237
exec sp_procxmode 'sp_memlog', 'AnyMode'
go
Grant Execute on sp_memlog to public
go
DEFECTS |
MEST 4 Empty String will be replaced by Single Space |
96 |
MEST 4 Empty String will be replaced by Single Space |
205 |
MINU 4 Unique Index with nullable columns master..sysattributes |
master..sysattributes |
MTYP 4 Assignment type mismatch attribute: smallint = int |
163 |
MTYP 4 Assignment type mismatch class: smallint = int |
163 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
153 |
QTYP 4 Comparison type mismatch smallint = int |
153 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
154 |
QTYP 4 Comparison type mismatch smallint = int |
154 |
QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int |
210 |
QTYP 4 Comparison type mismatch smallint = int |
210 |
QTYP 4 Comparison type mismatch Comparison type mismatch: int vs smallint |
212 |
MGTP 3 Grant to public master..sysattributes |
|
MGTP 3 Grant to public sybsystemprocs..sp_memlog |
|
MNER 3 No Error Check should check @@error after insert |
71 |
MNER 3 No Error Check should check @@error after insert |
72 |
MNER 3 No Error Check should check @@error after insert |
73 |
MNER 3 No Error Check should check @@error after insert |
74 |
MNER 3 No Error Check should check @@error after insert |
75 |
MNER 3 No Error Check should check @@error after insert |
76 |
MNER 3 No Error Check should check @@error after insert |
82 |
MNER 3 No Error Check should check @@error after insert |
83 |
MNER 3 No Error Check should check @@error after insert |
84 |
MNER 3 No Error Check should check @@error after insert |
85 |
MNER 3 No Error Check should check @@error after insert |
86 |
MNER 3 No Error Check should check @@error after delete |
152 |
MUCO 3 Useless Code Useless Brackets |
116 |
MUCO 3 Useless Code Useless Brackets |
166 |
MUCO 3 Useless Code Useless Brackets |
184 |
MUCO 3 Useless Code Useless Brackets |
224 |
MUIN 3 Column created using implicit nullability |
69 |
QAFM 3 Var Assignment from potentially many rows |
124 |
QIWC 3 Insert with not all columns specified missing 10 columns out of 15 |
161 |
QJWT 3 Join or Sarg Without Index on temp table |
212 |
QNAJ 3 Not using ANSI Inner Join |
208 |
QNUA 3 Should use Alias: Column object_type should use alias att |
211 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type, attribute} |
153 |
QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysattributes.csysattributes unique clustered (class, attribute, object_type, object, object_info1, object_info2, object_info3, object_cinfo) Intersection: {class, object_type} |
210 |
VNRD 3 Variable is not read @ATTR_ADD |
52 |
VNRD 3 Variable is not read @ATTR_DROP |
54 |
VNRD 3 Variable is not read @ATTR_FETCH |
55 |
MRST 2 Result Set Marker |
206 |
MTR1 2 Metrics: Comments Ratio Comments: 41% |
17 |
MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 8dec - 3exi + 2 |
17 |
MTR3 2 Metrics: Query Complexity Complexity: 96 |
17 |