DatabaseProcApplicationCreatedLinks
sybsystemprocssp_logging_rate  14 déc. 14Defects Propagation Dependencies

1     
2     /*
3     ** Messages for "sp_logging_rate"
4     **
5     ** 17199, Invalid parameter '%1!'.
6     ** 17208, Usage: sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, @interval='hh:mm:ss'][,clear_option='y'|'n']
7     ** 17209, Illegal format: '%1!'.
8     ** 17219, Can't start sampling because interval time '%1!' is logner than duration time '%2!'.
9     */
10    
11    /*
12    ** This procedure calculates the growth of transactional log and is 
13    ** reported in GB/Hr.
14    **
15    ** Arguments:
16    **  @print_option = full - detailed report for each collection.
17    **                  sum  - summary information (Avg, Min, Max rate)
18    ** 
19    **  @duration = The day,hh:mm:ss format to determine how long we will monitor
20    **              and collect values
21    **  @interval = Optional, default is 00:00:10, determine interval time for
22    **              doing sampling for each collection
23    **  @clear_option = y - clear the monitor counter for sample period
24    **                  n - not clear the monitor counter for sample period
25    **
26    ** Returns:
27    **  0   - Successful execution.
28    **  1   - If any error happens
29    **
30    ** Usage:
31    **  sp_logging_rate {'full'|'sum', '[day,]hh:mm:ss'}[, @interval='hh:mm:ss'][,clear_option='y'|'n']
32    **
33    */
34    
35    create or replace procedure sp_logging_rate
36        @print_option char(5) = 'sum', /* Option for print full information or summary only */
37        @duration char(12) = '00:10:00', /* Time duration for sampling, format is "day,hh:mm:ss". e.g "12,01:00:00" */
38        @interval char(8) = '00:00:10', /* Time for checking the counter in monitor table */
39        @clear_option char(1) = 'n' /* Option for whether clear counter */
40    as
41        declare @return int /* return value */
42        declare @rptline char(100) /* formatted stats line for print statement */
43        declare @sumlograte double precision /* total summary trans log rate */
44        declare @minlograte double precision /* total minimum trans log rate  */
45        declare @maxlograte double precision /* total maximum trans log rate  */
46        declare @avglograte double precision /* total average trans log rate  */
47        declare @slog double precision /* Trans log alloc page counter initial value from sysmonitors table */
48        declare @elog double precision /* Trans log alloc page counter value from sysmonitors table */
49        declare @lograte double precision /* Trans log growth rate for each sampling time */
50        declare @stime datetime /* start time for time interval */
51        declare @etime datetime /* end time for time interval */
52        declare @i int
53        declare @duration_day int
54        declare @duration_time char(8)
55        declare @counter int
56        declare @tmpstr char(20)
57        declare @rptusage char(110)
58    
59        select @sumlograte = 0
60        select @minlograte = 0
61        select @maxlograte = 0
62        select @avglograte = 0
63        select @i = 1
64    
65        exec sp_getmessage 17208, @rptusage output
66    
67        /* If print option is not valid */
68        if (@print_option != "full" and @print_option != "sum")
69        begin
70            raiserror 17199, @print_option
71            print @rptusage
72            return 1
73        end
74    
75        /*
76        ** If the duration_time string is not valid, print error and exit
77        */
78        select @duration_day = 0
79        select @duration_time = rtrim(ltrim(@duration))
80        select @i = charindex(",", @duration_time)
81        if @i > 1
82        begin
83            select @tmpstr = substring(@duration_time, 1, @i - 1)
84            begin
85                select @duration_day = convert(int, @tmpstr)
86                select @tmpstr = @duration_time
87                select @duration_time = substring(@tmpstr, @i + 1, 8)
88            end
89        end
90    
91        if (patindex("[0-2][0-3]:[0-5][0-9]:[0-5][0-9]", @duration_time) = 0)
92        begin
93            raiserror 17209, @duration
94            print @rptusage
95            return 1
96        end
97    
98        /*
99        ** If the interval string is not valid, print error and exit
100       */
101       if (patindex("[0-2][0-3]:[0-5][0-9]:[0-5][0-9]", @interval) = 0)
102       begin
103           raiserror 17209, @interval
104           print @rptusage
105           return 1
106       end
107   
108       /* Caculate the end sample time */
109       select @counter = ceiling((datediff(ss, "00:00:00", @duration_time) + @duration_day * 3600) / datediff(ss, "00:00:00", @interval))
110       if (@counter < 1)
111       begin
112           raiserror 17219, @interval, @duration
113           return 1
114       end
115   
116       /*
117       ** We do not allow to run if inside a transaction.
118       */
119       if @@trancount > 0
120       begin
121           print "Cannot run if within a transaction."
122           return 1
123       end
124       else
125           set chained off
126       set transaction isolation level 1
127   
128       select @i = 1
129   
130       while (@i <= @counter)
131       begin
132           if (@clear_option = "y")
133           begin
134               /*
135               ** Clear monitor counter values 
136               */
137               dbcc monitor("clear", "all", "on")
138               select @stime = getdate(), @slog = 0
139           end
140           else
141           begin
142               dbcc monitor("sample", "all", "on")
143               dbcc monitor("select", "all", "on")
144               /* Store the start time, initial counter for sampling */
145               select @stime = getdate(), @slog = SUM(value) from master.dbo.sysmonitors where group_name = "access" and field_name = "log_page_allocations"
146           end
147   
148           waitfor delay @interval
149   
150           dbcc monitor("sample", "all", "on")
151           dbcc monitor("select", "all", "on")
152           /* Store the end time, counter from sysmonitors table */
153           select @etime = getdate(), @elog = SUM(value) from master.dbo.sysmonitors where group_name = "access" and field_name = "log_page_allocations"
154   
155           /* Calculate the transaction log growth rate during the sample periods */
156           select @lograte = ((@elog - @slog) * (@@maxpagesize / 1024.00)) / (datediff(ms, @stime, @etime) / 1000.00) / ((1024.00 * 1024.00) / 3600)
157   
158           /* For the summary value */
159           select @sumlograte = @sumlograte + @lograte
160   
161           if @i > 1
162           begin
163               if @lograte < @minlograte
164                   select @minlograte = @lograte
165               if @lograte > @maxlograte
166                   select @maxlograte = @lograte
167           end
168           else
169           begin
170               select @minlograte = @lograte
171               select @maxlograte = @lograte
172               if @print_option = "full"
173               begin
174                   print "Date Time                       Transaction Log Growth Rate GB/h"
175                   print "---------------------------     --------------------------------"
176               end
177           end
178   
179           if @print_option = "full"
180           begin
181               select @rptline = convert(char(32), @etime, 109) + space(5) + str(@lograte, 14, 6)
182               print @rptline
183           end
184   
185           select @i = @i + 1
186       end
187   
188       /*
189       ** End Sampling
190       */
191       dbcc monitor("sample", "all", "off")
192   
193       select @avglograte = @sumlograte / (@i - 1.0)
194   
195       /* Print summary information */
196       print " "
197       print "========================="
198       print "Total Summary Information"
199       print "========================="
200       print "Transaction Log Growth Rate      Min GB/h        Max GB/h        Avg GB/h"
201       print "---------------------------   --------------  --------------  --------------"
202       select @rptline = space(28) + str(@minlograte, 14, 6) + space(2) + str(@maxlograte, 14, 6) + space(2) + str(@avglograte, 14, 6)
203       print @rptline
204   
205       return 0
206   
207   

DEFECTS
 TNOI 4 Table with no index master..sysmonitors master..sysmonitors
 MGTP 3 Grant to public master..sysmonitors  
 MLCH 3 Char type with length>30 char(100) 42
 MLCH 3 Char type with length>30 char(110) 57
 MLCH 3 Char type with length>30 char(32) 181
 MNER 3 No Error Check should check return value of exec 65
 MUCO 3 Useless Code Useless Brackets 68
 MUCO 3 Useless Code Useless Brackets 91
 MUCO 3 Useless Code Useless Brackets 101
 MUCO 3 Useless Code Useless Brackets 110
 MUCO 3 Useless Code Useless Brackets 130
 MUCO 3 Useless Code Useless Brackets 132
 QISO 3 Set isolation level 126
 VUNU 3 Variable is not used @return 41
 MTR1 2 Metrics: Comments Ratio Comments: 35% 35
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 13 = 17dec - 6exi + 2 35
 MTR3 2 Metrics: Query Complexity Complexity: 112 35

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_getmessage  
   reads table master..syslanguages (1)  
   reads table sybsystemprocs..sysusermessages  
   calls proc sybsystemprocs..sp_validlang  
      reads table master..syslanguages (1)  
   reads table master..sysmessages (1)  
reads table master..sysmonitors (1)