1 2 /*
3 ** Messages for "sp_dbcc_scale_factor"
4 */5 6 /*
7 ** This procedure takes three parameters
8 ** a size in kilobytes (input)
9 ** a scale factor (1024 or 1024 * 1024) (output)
10 ** a display unit, char 'K', 'M', 'G' (output)
11 **
12 ** The cutoff boundaries are set at
13 ** > 10 G (10485760 k) => G, scale 1024 * 1024
14 ** > 10 M (10240 k) => M, scale 1024
15 ** else K, scale 1
16 **
17 ** Call using
18 ** execute sp_dbcc_scale_factor ,
19 ** @scale_factor = @ output,
20 ** @scale_letter = @ output
21 **
22 ** where name for factor represents ant int
23 ** name for unit represents a char(1)
24 **
25 ** It is installed in sybsystemprocs for use by
26 ** sp_plan_dbccdb and later for use if dbccdb is installed
27 ** by sp_dbcc_evaluatedb.
28 **
29 ** RESOLVE:
30 ** Future Issue:
31 **
32 ** size_in_k is an int.
33 ** int on SQL server is 2^31 max ~ 2 10^9
34 ** hence will not allow us to go past 2 10^12 bytes
35 ** or 2 TB. This can be solved my making the input a
36 ** numeric, in which case callers would also need
37 ** to use a numeric as input value. We will not address this here.
38 */39 40 createproceduresp_dbcc_scale_factor41 (42 @size_in_k int,43 @scale_factor int output,44 @scale_letter char(1)output45 )46 as47 begin48 if @@trancount = 0
49 begin50 set chained off51 end52 53 settransactionisolationlevel 1
54 set nocount on55 if(@size_in_k> 10485760)56 begin57 select@scale_factor=(1024 * 1024)58 select@scale_letter= 'G'
59 end60 else61 begin62 if(@size_in_k> 10240)63 begin64 select@scale_factor= 1024
65 select@scale_letter= 'M'
66 end67 else68 begin69 select@scale_factor= 1
70 select@scale_letter= 'K'
71 end72 end73 end74
exec sp_procxmode 'sp_dbcc_scale_factor', 'AnyMode'
go
Grant Execute on sp_dbcc_scale_factor to public
go