DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceused_syslogs  14 déc. 14Defects Propagation Dependencies

1     
2     create or replace procedure sp_spaceused_syslogs(
3         @total_pages bigint output
4         , @free_pages bigint output
5         , @used_pages bigint output
6         , @used_pages_wo_APs bigint output
7         , @clr_pages bigint output
8     ) as
9         begin
10            declare @ismixedlog int
11                , @dbid int
12    
13            set @dbid = db_id()
14    
15            select @ismixedlog = (db.status2 & 32768)
16            from master.dbo.sysdatabases db
17            where db.dbid = @dbid
18    
19            select @clr_pages = lct_admin("reserved_for_rollbacks", @dbid)
20            select @free_pages = lct_admin("logsegment_freepages", @dbid)
21                - @clr_pages
22    
23            select @total_pages = sum(u.size)
24            from master.dbo.sysusages u
25            where u.segmap & 4 = 4
26                and u.dbid = @dbid
27    
28            if (@ismixedlog = 32768)
29            begin
30                /* 
31                ** For a mixed log and data database, we cannot
32                ** deduce the log used space from the total space
33                ** as it is mixed with data. So we take the expensive
34                ** way of scanning syslogs, AP-by-AP and then extent-by-extent.
35                */
36                select @used_pages_wo_APs = lct_admin("num_logpages", @dbid)
37    
38                /* Account allocation pages as used pages */
39                select @used_pages = @used_pages_wo_APs + (@total_pages / 256)
40            end
41            else
42            begin
43                /* Dedicated log database */
44                select @used_pages = @total_pages - @free_pages - @clr_pages
45    
46                /* See note (1) above */
47                set @used_pages_wo_APs =
48                    (@total_pages - (@total_pages / 256) - @free_pages)
49            end
50    
51        end -- }
52    


exec sp_procxmode 'sp_spaceused_syslogs', 'AnyMode'
go

Grant Execute on sp_spaceused_syslogs to public
go
DEFECTS
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 17
 QTYP 4 Comparison type mismatch smallint = int 17
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 26
 QTYP 4 Comparison type mismatch smallint = int 26
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_spaceused_syslogs  
 MUCO 3 Useless Code Useless Brackets in create proc 2
 MUCO 3 Useless Code Useless Begin-End Pair 9
 MUCO 3 Useless Code Useless Brackets 28
 MUCO 3 Useless Code Useless Brackets 39
 MUCO 3 Useless Code Useless Brackets 48
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
25
 MTR1 2 Metrics: Comments Ratio Comments: 21% 2
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 4 = 3dec - 1exi + 2 2
 MTR3 2 Metrics: Query Complexity Complexity: 18 2

DEPENDENCIES
PROCS AND TABLES USED
reads table master..sysdatabases (1)  
reads table master..sysusages (1)  

CALLERS
called by proc sybsystemprocs..sp_helpsegment  
called by proc sybsystemprocs..sp_spaceused