| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_spaceusage_tranlog_init | | 31 Aug 14 | Defects 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 |