DatabaseProcApplicationCreatedLinks
sybsystemprocssp_spaceusage_tranlog_init  31 Aug 14Defects Dependencies

1     
2     /*
3     **	SP_SPACEUSAGE_TRANLOG_INIT
4     **
5     **	The sub-procedure that initializes the temporary table #syslogsinfo
6     **	with space usage information depending on the action. For "display" or
7     **	or "archive" action, it gets data using the lct_admin() built-in
8     **	and system tables and loads into #syslogsinfo (any other derived data,
9     **	if needed, will have to be computed in the next step to make the
10    **	information complete) whereas for the action "report", the data in the
11    **	archive table is loaded into the #syslogsinfo table. Called by 
12    **	sp_spaceusage_tranlog.
13    **
14    **	Parameters
15    **		@actionword	- The action to be performed.
16    **		@archivetabname	- The 3-part archive table name.
17    **		@from_date	- Date from when on to get archived data.
18    **		@to_date	- Date till which to get archvied data.
19    **
20    **	Returns
21    **		0 - if all goes well
22    **		6 - archive table not found
23    **	    other - error while execution 
24    {
25    */
26    create procedure sp_spaceusage_tranlog_init
27    (
28        @actionword varchar(20)
29        , @archivetabname varchar(320) = NULL
30        , @from_date varchar(30) = NULL
31        , @to_date varchar(30) = NULL
32    )
33    as
34        begin -- {
35    
36            declare @action_display varchar(8)
37                , @action_report varchar(7)
38                , @action_archive varchar(8)
39    
40                , @esdnumstr varchar(20)
41                , @ebfnumstr varchar(20)
42                , @esdnum int
43                , @ebfnum int
44    
45                , @ismld bit
46                , @totalpages int
47    
48                , @returnStatus int
49                , @sqlstmt varchar(4096)
50                , @whoami varchar(40)
51                , @msg varchar(256)
52    
53    
54            select @whoami = "sp_spaceusage_tran_init"
55    
56                , @action_display = "display"
57                , @action_report = "report"
58                , @action_archive = "archive"
59    
60            /* Extract the ESD number and the EBF number from the version string.*/
61            exec sp_versioncrack @@version, "ESD", @esdnumstr out, @esdnum out
62            exec sp_versioncrack @@version, "EBF", @ebfnumstr out, @ebfnum out
63    
64            if @actionword in (@action_display, @action_archive)
65            begin -- { 	-- Display/Archive mode
66    
67                /* Collect all the necessary raw data or meta data. */
68                select @ismld = case (status2 & 32768)
69                        when 32768 then 1
70                        else 0
71                    end
72                from master.dbo.sysdatabases
73                where dbid = db_id()
74    
75                select @totalpages = sum(size)
76                from master.dbo.sysusages
77                where dbid = db_id()
78                    and segmap & 4 = 4
79    
80                insert into #syslogsinfo
81                select
82                    VersionNum = @@version_number
83                    , ESDNum = @esdnum
84                    , EBFNum = @ebfnum
85                    , ArchiveDateTime = getdate()
86                    , ServerName = @@servername
87                    , MaxPageSize = @@maxpagesize
88                    , DBName = db_name()
89                    , OwnerName = "dbo"
90                    , TableName = "syslogs"
91                    , Id = object_id("syslogs")
92    
93                    -- This is a placeholder for the number of rows in
94                    -- syslogs. syslogs does not have an oam and thus
95                    -- getting this value is, currently, expensive. This
96                    -- column may be used in future.
97                    -- 
98                    , NumRows = 0
99    
100                   -- Is the log mixed with data?
101                   , IsMLD = @ismld
102   
103                   -- Is the log full (crossed LCT)?
104                   , IsLogFull =
105                   convert(bit, lct_admin("logfull", db_id()))
106   
107                   -- The current value for the LCT in pages.
108                   , LCTPageCount = lct_admin("reserve", 0)
109   
110                   -- Total pages available for the log. 
111                   , TotalPageCount = @totalpages
112   
113                   -- This is a placeholder for the number of pages
114                   -- reserved for log in the database as is returned by
115                   -- the reserved_pages() built-in. The built-in uses an
116                   -- expensive allocation page scan to get this value. 
117                   -- This column may be used in future.
118                   --
119                   , RsvdPageCount = 0
120   
121                   -- Pages reserved for rollback.
122                   , CLRPageCount =
123                   lct_admin("reserved_for_rollbacks", db_id())
124   
125                   -- The number of transaction log pages as of now. It is
126                   -- initially set to 0 and will be updated in the next
127                   -- step as it may depend on the TotalPageCount and 
128                   -- FreePageCount columns. 
129                   , UsedPageCount = 0
130   
131                   -- The number of pages free for future log.
132                   , FreePageCount =
133                   lct_admin("logsegment_freepages", db_id())
134   
135                   -- Percentage free/used pages (derived columns)
136                   , PctFreePages = convert(numeric(5, 2), 0)
137                   , PctUsedPages = convert(numeric(5, 2), 0)
138   
139                   -- The placeholder for pagecounts in units. 
140                   , LCTPages = convert(float, 0)
141                   , TotalPages = convert(float, 0)
142                   , RsvdPages = convert(float, 0)
143                   , CLRPages = convert(float, 0)
144                   , UsedPages = convert(float, 0)
145                   , FreePages = convert(float, 0)
146   
147               if (@@error != 0)
148                   return (@@error)
149   
150               -- Update page counts that are dependant on other page counts.
151               update #syslogsinfo
152               set FreePageCount = FreePageCount - CLRPageCount
153               where CLRPageCount > 0
154   
155               update #syslogsinfo
156               set UsedPageCount =
157                   case IsMLD
158                       when 1
159                       then lct_admin("num_logpages", db_id())
160                       + -- Count the allocation pages.
161                       TotalPageCount / 256.0
162                       else TotalPageCount - FreePageCount
163                           - CLRPageCount
164                   end
165   
166               -- Update the percentage value for the used and free pages. 
167               update #syslogsinfo
168               set PctFreePages = convert(numeric(5, 2),
169                   FreePageCount * 100.0
170                   / TotalPageCount)
171                   , PctUsedPages = convert(numeric(5, 2),
172                   UsedPageCount * 100.0
173                   / TotalPageCount)
174               where TotalPageCount > 0
175   
176               if (@@error != 0)
177                   return (@@error)
178   
179           end -- }	-- Display/Archive mode
180   
181           else if @actionword = @action_report
182   
183           begin -- {	-- Report action
184   
185               -- Check for valid archive table name is already done.
186   
187               select @sqlstmt = " INSERT INTO #syslogsinfo "
188                   + " SELECT 	 * "
189                   + ", LCTPages" + "=0"
190                   + ", TotalPages" + "=0"
191                   + ", RsvdPages" + "=0"
192                   + ", CLRPages" + "=0"
193                   + ", UsedPages" + "=0"
194                   + ", FreePages" + "=0"
195                   + " FROM " + @archivetabname + " o "
196                   + " WHERE DBName LIKE '" + db_name() + "'"
197   
198               -- The ArchiveDateTime part of the query does the following.
199               -- When both @from_date and @to_date are NULL, it looks for the
200               -- latest ArchiveDateTime for each object that qualifies and
201               -- selects that entry into the #syslogsinfo table. If only
202               -- @from_date is NULL, it selects all data till @to_date. And
203               -- when only @to_date is missing, it assumes @to_date to be
204               -- today.
205               --
206               if @from_date is NULL
207               begin
208                   if @to_date is NULL
209                   begin -- both dates are null
210                       select @sqlstmt = @sqlstmt
211                           + " AND ArchiveDateTime "
212                           + " = (SELECT "
213                           + "max(ArchiveDateTime)"
214                           + " FROM " + @archivetabname
215                           + " i2 "
216                           + " WHERE i2.DBName=o.DBName"
217                           + " AND i2.OwnerName"
218                           + "=o.OwnerName"
219                           + " AND i2.TableName"
220                           + "=o.TableName"
221                           + " AND (i2.ServerName is NULL"
222                           + " or i2.ServerName"
223                           + "=o.ServerName)"
224                           + ")"
225   
226                   end
227                   else
228                   begin -- only from_date is null
229                       select @sqlstmt = @sqlstmt
230                           + " AND ArchiveDateTime "
231                           + " <= '" + @to_date + "'"
232                   end
233               end
234               else
235               begin
236                   if @to_date is NULL
237                   begin -- only to_date is null
238                       select @sqlstmt = @sqlstmt
239                           + " AND ArchiveDateTime "
240                           + " >= '" + @from_date + "'"
241                           + " AND ArchiveDateTime "
242                           + " <= getdate()"
243                   end
244                   else
245                   begin -- both dates are non-null
246                       select @sqlstmt = @sqlstmt
247                           + " AND ArchiveDateTime "
248                           + " >= '" + @from_date + "'"
249                           + " AND ArchiveDateTime "
250                           + " <= '" + @to_date + "'"
251                   end
252               end
253   
254               exec @returnStatus = sp_exec_SQL @sqlstmt, @whoami
255   
256               if @returnStatus != 0
257                   return (@returnStatus)
258   
259           end -- } 	-- Report action 
260   
261           -- If no row in #syslogsinfo, no object qualified and hence report an
262           -- error message indicating the same and return back.
263           --
264           if not exists (select 1 from #syslogsinfo)
265           begin
266               declare @tmpdbname varchar(30)
267               select @tmpdbname = db_name()
268   
269               raiserror 19576, "'syslogs'", @tmpdbname
270               return (7)
271           end
272           return (0)
273   
274       end -- }	-- }
275   


exec sp_procxmode 'sp_spaceusage_tranlog_init', 'AnyMode'
go

Grant Execute on sp_spaceusage_tranlog_init to public
go
DEFECTS
 MTYP 4 Assignment type mismatch @callerID: varchar(30) = varchar(40) 254
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 73
 QTYP 4 Comparison type mismatch Comparison type mismatch: smallint vs int 77
 MGTP 3 Grant to public master..sysdatabases  
 MGTP 3 Grant to public master..sysusages  
 MGTP 3 Grant to public sybsystemprocs..sp_spaceusage_tranlog_init  
 MNER 3 No Error Check should check return value of exec 61
 MNER 3 No Error Check should check return value of exec 62
 MNER 3 No Error Check should check @@error after update 151
 MNER 3 No Error Check should check @@error after update 155
 MUCO 3 Useless Code Useless Brackets in create proc 27
 MUCO 3 Useless Code Useless Begin-End Pair 34
 MUCO 3 Useless Code Useless Brackets 147
 MUCO 3 Useless Code Useless Brackets 148
 MUCO 3 Useless Code Useless Brackets 176
 MUCO 3 Useless Code Useless Brackets 177
 MUCO 3 Useless Code Useless Brackets 257
 MUCO 3 Useless Code Useless Brackets 270
 MUCO 3 Useless Code Useless Brackets 272
 QPRI 3 Join or Sarg with Rooted Partial Index Use SARG Candidate index: sysusages.csysusages unique clustered
(dbid, lstart)
Intersection: {dbid}
77
 VNRD 3 Variable is not read @esdnumstr 61
 VNRD 3 Variable is not read @ebfnumstr 62
 VUNU 3 Variable is not used @msg 51
 MTR1 2 Metrics: Comments Ratio Comments: 37% 26
 MTR2 2 Metrics: Cyclomatic Complexity Cyclo: 7 = 10dec - 5exi + 2 26
 MTR3 2 Metrics: Query Complexity Complexity: 55 26

DEPENDENCIES
PROCS AND TABLES USED
calls proc sybsystemprocs..sp_versioncrack  
   calls proc sybsystemprocs..sp_split_string  
reads table master..sysusages (1)  
calls proc sybsystemprocs..sp_exec_SQL  
read_writes table tempdb..#syslogsinfo (1) 
reads table master..sysdatabases (1)  

CALLERS
called by proc sybsystemprocs..sp_spaceusage_tranlog  
   called by proc sybsystemprocs..sp_spaceusage