| Database | Proc | Application | Created | Links |
| sybsystemprocs | sp_shmdumpdisp | | 31 Aug 14 | Defects Dependencies |
1 2 /* 3 ** SP_SHMDUMPDISP 4 ** 5 ** This stored procedure is used to display the current setting for 6 ** shared memory dump conditions. It is called by the sp_shmdumpconfig 7 ** stored procedure when a user executes sp_shmdumpconfig to display 8 ** the current settings. 9 ** 10 ** This stored procedure calls the sp_shmdumpsize stored procedure 11 ** in order to get size estimates for the shared memory dump files 12 ** that will be generated for each condition. 13 ** 14 ** History: 15 ** 19sept96 pdorfman Initial coding 16 ** 07apr97 pdorfman Created separate stored procedure 17 ** 21jan02 dwein Added support for message type 18 ** 19apr04 pdorfman Skip dump count for default condition 19 */ 20 21 create procedure sp_shmdumpdisp 22 /* -------------- Stored Procedure Parameters ----------------------- */ 23 @type varchar(20) /* action requested by user */ 24 as 25 26 /* ----------------- Declare Local Variables ------------------------ */ 27 declare @not_status int, /* notification return status */ 28 @maxconds int, /* max # dump conditions */ 29 @curcount int, /* current # dump cond's */ 30 @header_printed int, /* display header printed */ 31 @print_str varchar(1024), /* for printing error msgs */ 32 @cache_size int, /* total data cache memory */ 33 @proc_size int, /* size of procedure cache */ 34 @memory int, /* server total memory */ 35 @shm_size int, /* non-cache memory */ 36 @proc_size_str char(9), /* size of procedure cache */ 37 @cache_size_str char(9), /* server total memory */ 38 @shm_size_str char(9), /* non-cache memory */ 39 @memory_str char(9), /* server total memory */ 40 @position int, /* position of first blank */ 41 @windows int, /* indicates windows platform */ 42 @rptline varchar(512) 43 44 45 46 /* 47 ** The following variables are treated as constants within this 48 ** stored procedure. They are set below. 49 */ 50 declare @DUMPCOND_CLASS int, /* sysattributes class */ 51 @OBJTYPE char(2), /* sysattributes object typ */ 52 @CFG_PRIMARY int, /* primary record id */ 53 @CFG_FILENAME int, /* file name record id */ 54 @CFG_DIRNAME int, /* directory name record id */ 55 @CFG_MAXDUMPS int, /* maxdumps record id */ 56 @CFG_PAGECACHE int, /* page cache record id */ 57 @CFG_PROCCACHE int, /* proc cache record id */ 58 @CFG_UNUSED int, /* unused memory record id */ 59 @CFG_HALT int, /* halt option record id */ 60 @ATTR_ADD int, /* add notification value */ 61 @ATTR_CHANGE int, /* change notification val */ 62 @ATTR_DROP int, /* drop notification value */ 63 @ATTR_FETCH int, /* fetch notification value */ 64 @CFG_INCLUDE int, /* include memory type */ 65 @CFG_OMIT int, /* omit memory type */ 66 @CFG_DEFAULT int, /* use default mem setting */ 67 @CFG_ATTRIBUTE int, /* attrib val for config options */ 68 @CFG_ERRORLOG int, /* include errorlog record id */ 69 @CFG_MERGE int, /* merge pll dump record id */ 70 @CFG_CLUSTER int /* cluster dump behavior */ 71 72 /* 73 ** Declare variables and cursors used to display current settings 74 */ 75 declare @cond_name char(10), 76 @cond_value int, 77 @cond_cvalue char(20), 78 @cond_attrib int, 79 @rectype int, 80 @int_val int, 81 @char_val varchar(255), 82 @cmaxdumps char(7), 83 @dmaxdumps char(7), 84 @cpagecache varchar(20), 85 @dpagecache varchar(20), 86 @cproccache varchar(20), 87 @dproccache varchar(20), 88 @cunused varchar(20), 89 @dunused varchar(20), 90 @chalt varchar(20), 91 @dhalt varchar(20), 92 @cdirname varchar(255), 93 @ddirname varchar(255), 94 @cfilename varchar(30), 95 @dfilename varchar(30), 96 @defaults_found int, 97 @dumpsize int, 98 @cdumpsize varchar(10), 99 @order_number int, 100 @dumpcount char(3), 101 @cfgval int, 102 @cluster char(7), 103 @dcluster char(7), 104 @current_mode varchar(10) 105 106 107 /* 108 ** Create temporary lookup table referenced by the c_conditions 109 ** cursor (below). Table creation is required here in order to 110 ** declare the cursor. 111 */ 112 create table #attname(attrib int, name char(10)) 113 114 /* 115 ** Create temporary conditions table referenced by the c_display 116 ** cursor (below). Table creation is required here in order to 117 ** declare the cursor 118 */ 119 create table #conditions 120 (order_num int, 121 Type char(10), 122 Value char(20) null, 123 Maxdumps char(7), 124 Dump_Count char(3) null, 125 Page_Cache char(7), 126 Proc_Cache char(7), 127 Unused_Space char(7), 128 Halt char(7), 129 Cluster char(7), 130 Est_File_Size varchar(10), 131 Filename varchar(30), 132 Directory varchar(255)) 133 134 /* 135 ** Cursor to return all dump condition rows, ordered by object type 136 ** (attribute) 137 */ 138 declare c_conditions cursor 139 for select an.name, sa.attribute, sa.object 140 from master.dbo.sysattributes sa, #attname an 141 where sa.class = @DUMPCOND_CLASS 142 and sa.object_type = @OBJTYPE 143 and sa.attribute = an.attrib 144 and sa.object_info1 = @CFG_PRIMARY 145 order by sa.attribute 146 147 /* 148 ** Cursor to return all settings for a given dump condition 149 */ 150 declare c_settings cursor 151 for select sa.object_info1, sa.int_value, sa.char_value 152 from master.dbo.sysattributes sa 153 where sa.class = @DUMPCOND_CLASS 154 and sa.object_type = @OBJTYPE 155 and sa.attribute = @cond_attrib 156 and sa.object = @cond_value 157 and sa.object_info1 != @CFG_PRIMARY 158 159 /* 160 ** Cursor to read the #conditions table and display the contents. 161 */ 162 declare c_display cursor 163 for select Type, Value, Maxdumps, Dump_Count, 164 Page_Cache, Proc_Cache, Unused_Space, 165 Halt, Cluster, Est_File_Size, Filename, Directory 166 from #conditions 167 order by order_num 168 169 /* ----------------- Setup and Validation ------------------------ */ 170 set nocount on 171 172 /* 173 ** Common Definition Section: Note: any changes made to the following 174 ** values must also be made in shmdumpdrop and shmdumpconfig 175 */ 176 177 /* 178 ** Class ID and type defined in utils/attrib.lst 179 */ 180 select @DUMPCOND_CLASS = 7 181 select @OBJTYPE = "DC" 182 183 /* 184 ** The following constants define record types for the dump condition 185 ** class in the sysattributes table.The values set here must be the 186 ** same as those defined in utils/cfgdump.c. 187 */ 188 select @CFG_PRIMARY = 1 189 select @CFG_FILENAME = 2 190 select @CFG_DIRNAME = 3 191 select @CFG_MAXDUMPS = 4 192 select @CFG_PAGECACHE = 5 193 select @CFG_PROCCACHE = 6 194 select @CFG_UNUSED = 7 195 select @CFG_HALT = 8 196 select @CFG_CLUSTER = 9 197 198 /* 199 ** The following constants define record types for the dump condition 200 ** class in the sysattributes table for the config records. The values 201 ** set here must be the same as those defined in utils/shmdump.c. 202 */ 203 select @CFG_ATTRIBUTE = 0 204 select @CFG_ERRORLOG = 1 205 select @CFG_MERGE = 2 206 207 /* 208 ** The following constants are used for setting memory modes and other 209 ** options. These values must correspond to those used in utils/cfgdump.c. 210 */ 211 select @CFG_DEFAULT = 0 /* Use the default value */ 212 select @CFG_INCLUDE = 1 /* Include memory / Option On */ 213 select @CFG_OMIT = 2 /* Omit memory / Option off */ 214 215 /* 216 ** The following must correspond to values in sysattr.h 217 */ 218 select @ATTR_ADD = 1 219 select @ATTR_CHANGE = 2 220 select @ATTR_DROP = 3 221 select @ATTR_FETCH = 4 222 223 /* 224 ** Determine if we are running on the windows platform 225 */ 226 if (select charindex("Windows", @@version)) > 0 227 begin 228 select @windows = 1 229 end 230 else 231 begin 232 select @windows = 0 233 end 234 235 236 /* 237 ** End Common Definition Section 238 */ 239 240 /* ----------------- Identify and Perform the Command ----------- */ 241 242 select @defaults_found = 0 243 select @header_printed = 0 244 245 /* 246 ** Create table #attname (attrib int, name char(25)) 247 ** NOTE: The atribute values must correspond to those defined 248 ** in utils/attrib.lst. 249 */ 250 insert #attname values (1, 'Error ') 251 insert #attname values (2, 'Signal ') 252 insert #attname values (3, 'Severity') 253 insert #attname values (4, 'Module ') 254 insert #attname values (5, 'Defaults') 255 insert #attname values (6, 'Timeslice') 256 insert #attname values (7, 'Panic') 257 insert #attname values (8, 'Message') 258 insert #attname values (9, 'Dbcc') 259 insert #attname values (10, 'Codepoint') 260 261 /* 262 ** Open curson to select all dump conditions 263 */ 264 open c_conditions 265 266 fetch c_conditions into @cond_name, @cond_attrib, @cond_value 267 268 if (@@sqlstatus = 1) 269 begin 270 /* 271 ** 18511, "Error reading sysattributes table" 272 */ 273 raiserror 18511 274 return 1 275 end 276 277 select @cmaxdumps = "Default" 278 select @cpagecache = "Default" 279 select @cproccache = "Default" 280 select @cunused = "Default" 281 select @chalt = "Default" 282 select @cfilename = "Default File Name" 283 select @cdirname = "Default Directory" 284 select @dumpcount = "" 285 select @cluster = "Default" 286 287 while (@@sqlstatus = 0) 288 begin 289 /* 290 ** Fetch all special settings for the current 291 ** dump condition 292 */ 293 open c_settings 294 295 fetch c_settings into @rectype, @int_val, @char_val 296 297 /* 298 ** There is no dump count for the Defaults condition 299 */ 300 if (@cond_name != "Defaults") 301 begin 302 select @dumpcount = convert(char(3), 303 shmdumpcount(rtrim(@cond_name), 304 @cond_value)) 305 end 306 else 307 begin 308 select @dumpcount = " 0" 309 end 310 311 while (@@sqlstatus = 0) 312 begin 313 if (@rectype = @CFG_MAXDUMPS) 314 begin 315 select @cmaxdumps = convert(char(7), @int_val) 316 end 317 else if (@rectype = @CFG_PAGECACHE) 318 begin 319 if (@int_val = 1) 320 select @cpagecache = 'Include' 321 else 322 select @cpagecache = 'Omit ' 323 end 324 else if (@rectype = @CFG_PROCCACHE) 325 begin 326 if (@int_val = 1) 327 select @cproccache = 'Include' 328 else 329 select @cproccache = 'Omit ' 330 end 331 else if (@rectype = @CFG_UNUSED) 332 begin 333 if (@int_val = 1) 334 select @cunused = 'Include' 335 else 336 select @cunused = 'Omit ' 337 end 338 else if (@rectype = @CFG_HALT) 339 begin 340 if (@int_val = 1) 341 select @chalt = 'Halt ' 342 else 343 select @chalt = 'No Halt' 344 end 345 else if (@rectype = @CFG_DIRNAME) 346 begin 347 select @cdirname = @char_val 348 end 349 else if (@rectype = @CFG_FILENAME) 350 begin 351 select @cfilename = @char_val 352 end 353 else if (@rectype = @CFG_CLUSTER) 354 begin 355 if (@int_val = 1) 356 select @cluster = 'All' 357 else 358 select @cluster = 'Local ' 359 end 360 else 361 begin 362 /* 363 ** 18512, "WARNING: Unknown dump condition record type found: %1!" 364 */ 365 raiserror 18512, @rectype 366 end 367 368 fetch c_settings 369 into @rectype, @int_val, @char_val 370 end /* while (@@sqlstatus = 0) */ 371 372 /* 373 ** Close cursor on settings for current condition 374 */ 375 close c_settings 376 377 select @cond_cvalue = convert(char(20), @cond_value) 378 379 /* 380 ** Handle the system default settings separately. 381 */ 382 if (@cond_attrib = 5) 383 begin 384 display_defaults: 385 select @defaults_found = 1 386 387 if (@header_printed = 1) 388 begin 389 print " " 390 end 391 392 /* 393 ** If the following values are not set by 394 ** records in the sysattributes table, report 395 ** the built in system defaults that we know will 396 ** be enforced at dump time (csmd_dump_condition) 397 ** using values hard-coded in the software. 398 */ 399 if (@cmaxdumps = 'Default') 400 begin 401 select @cmaxdumps = '1 ' 402 end 403 select @dmaxdumps = @cmaxdumps 404 405 if (@cpagecache = 'Default') 406 begin 407 select @cpagecache = 'Omit ' 408 end 409 select @dpagecache = @cpagecache 410 411 if (@cproccache = 'Default') 412 begin 413 select @cproccache = 'Include' 414 end 415 select @dproccache = @cproccache 416 417 if (@cunused = 'Default') 418 begin 419 select @cunused = 'Omit ' 420 end 421 select @dunused = @cunused 422 423 if (@chalt = 'Default') 424 begin 425 select @chalt = 'Halt ' 426 end 427 select @dhalt = @chalt 428 429 if (@cfilename = 'Default File Name') 430 begin 431 select @cfilename = 'Generated File Name' 432 end 433 select @dfilename = @cfilename 434 435 if (@cdirname = 'Default Directory') 436 begin 437 select @cdirname = '$SYBASE' 438 end 439 select @ddirname = @cdirname 440 441 if (@cluster = 'Default') 442 begin 443 select @cluster = 'Local ' 444 end 445 select @dcluster = @cluster 446 447 select @dumpcount = "---" 448 449 /* 450 ** Make sure that default settings display last in 451 ** the output. 452 */ 453 select @order_number = 9999 454 end 455 else /* (@cond_attrib = 5) */ 456 begin 457 /* 458 ** Group dump conditions by condition type 459 */ 460 select @order_number = @cond_attrib 461 end /* (@cond_attrib = 5) */ 462 463 /* 464 ** The value column is not applicable to the 465 ** system defaults, timeslice or panic 466 */ 467 if (@cond_attrib in (5, 6, 7, 9)) 468 begin 469 select @cond_cvalue = '--- ' 470 end 471 472 473 474 /* 475 ** Calculate the estimated dump file size 476 */ 477 exec sp_shmdumpsize @cpagecache, @cproccache, @dumpsize output 478 479 480 481 /* 482 ** Convert @dumpsize to varchar and append the units specification to 483 ** the string 484 */ 485 select @cdumpsize = (convert(varchar(10), @dumpsize) + " " + "MB") 486 487 /* 488 ** Insert data for curent condition into worktable for 489 ** display later. 490 */ 491 insert #conditions 492 values (@order_number, 493 @cond_name, 494 @cond_cvalue, 495 @cmaxdumps, 496 @dumpcount, 497 @cpagecache, 498 @cproccache, 499 @cunused, 500 @chalt, 501 @cluster, 502 @cdumpsize, 503 @cfilename, 504 @cdirname) 505 506 /* 507 ** Fetch next dump condition 508 */ 509 fetch c_conditions into @cond_name, @cond_attrib, @cond_value 510 511 /* 512 ** Set to default values 513 */ 514 select @cmaxdumps = "Default" 515 select @cpagecache = "Default" 516 select @cproccache = "Default" 517 select @cunused = "Default" 518 select @chalt = "Default" 519 select @cfilename = "Default File Name" 520 select @cdirname = "Default Directory" 521 select @dumpcount = "" 522 select @cluster = "Default" 523 end /* while (@@sqlstatus = 0) */ 524 525 /* 526 ** If we didn't find a defaults record, go back and display default 527 ** values anyway. This is as bit of a kludge, but it will work as long 528 ** as the goto statement is executed before the close cursor 529 ** statement that follows. 530 */ 531 if (@defaults_found = 0) 532 begin 533 /* 534 ** There is no entry for the default settings in the 535 ** sysattributes table. Display the system defaults 536 ** that are defined in the server. 537 */ 538 select @cond_name = 'Defaults' 539 select @cmaxdumps = '1 ' 540 if (@windows = 1) 541 begin 542 select @cdirname = "%%SYBASE%%" 543 end 544 else 545 begin 546 select @cdirname = "$SYBASE" 547 end 548 select @cond_attrib = 5 549 goto display_defaults 550 end 551 552 /* 553 ** Close cursor on dump conditions 554 */ 555 close c_conditions 556 557 /* 558 ** Now actually display the settings 559 */ 560 561 /* 562 ** Display a list of configured dump conditions 563 */ 564 print "" 565 print "Configured Shared Memory Dump Conditions" 566 print "----------------------------------------" 567 print "" 568 569 open c_display 570 571 fetch c_display into 572 @cond_name, 573 @cond_cvalue, 574 @cmaxdumps, 575 @dumpcount, 576 @cpagecache, 577 @cproccache, 578 @cunused, 579 @chalt, 580 @cluster, 581 @cdumpsize, 582 @cfilename, 583 @cdirname 584 585 while (@@sqlstatus = 0) 586 begin 587 if (@windows = 1 and @cond_name = "Signal") 588 begin 589 select @rptline = space(2) + "Exception" + space(1) + 590 "0x" + inttohex(convert(int, @cond_cvalue)) 591 end 592 else 593 begin 594 select @rptline = space(2) + @cond_name + space(1) + @cond_cvalue 595 end 596 print @rptline 597 598 if (@cond_name = "Message") 599 begin 600 select @print_str = space(4) + 601 "Message Text:" + space(11) + 602 str_replace(ssel_message(convert(int, 603 @cond_cvalue)), "%", "%%") 604 print @print_str 605 end 606 else if (@cond_name = "Error") 607 begin 608 if (@@langid = 0) 609 begin 610 select @print_str = space(4) + "Error Text" 611 + space(14) + 612 str_replace(description, "%", "%%") 613 from master.dbo.sysmessages where 614 error = convert(int, @cond_cvalue) 615 and langid is NULL 616 end 617 else 618 begin 619 select @print_str = space(4) + "Error Text" 620 + space(14) + 621 str_replace(description, "%", "%%") 622 from master.dbo.sysmessages where 623 error = convert(int, @cond_cvalue) 624 and langid = @@langid 625 end 626 print @print_str 627 end 628 629 select @rptline = space(4) + "Maximum Dumps:" + 630 space(10) + @cmaxdumps 631 if (@cond_name != "Defaults" and @cmaxdumps = "Default") 632 select @rptline = @rptline + " (" + rtrim(@dmaxdumps) + ")" 633 print @rptline 634 635 if (@cond_name != "Defaults") 636 begin 637 select @rptline = space(4) + "Dumps since boot:" + 638 space(7) + @dumpcount 639 print @rptline 640 end 641 642 select @rptline = space(4) + "Halt Engines:" + 643 space(11) + @chalt 644 if (@cond_name != "Defaults" and @chalt = "Default") 645 select @rptline = @rptline + " (" + rtrim(@dhalt) + ")" 646 print @rptline 647 648 select @rptline = space(4) + "Cluster:" + 649 space(16) + @cluster 650 if (@cond_name != "Defaults" and @cluster = "Default") 651 select @rptline = @rptline + " (" + rtrim(@dcluster) + ")" 652 print @rptline 653 654 select @rptline = space(4) + "Page Cache:" + 655 space(13) + @cpagecache 656 if (@cond_name != "Defaults" and @cpagecache = "Default") 657 select @rptline = @rptline + " (" + rtrim(@dpagecache) + ")" 658 print @rptline 659 660 select @rptline = space(4) + "Procedure Cache:" + 661 space(8) + @cproccache 662 if (@cond_name != "Defaults" and @cproccache = "Default") 663 select @rptline = @rptline + " (" + rtrim(@dproccache) + ")" 664 print @rptline 665 666 select @rptline = space(4) + "Unused Space:" + 667 space(11) + @cunused 668 if (@cond_name != "Defaults" and @cunused = "Default") 669 select @rptline = @rptline + " (" + rtrim(@dunused) + ")" 670 print @rptline 671 672 select @rptline = space(4) + "Dump Directory:" + 673 space(9) + @cdirname 674 if (@cond_name != "Defaults" and @cdirname = "Default Directory") 675 select @rptline = @rptline + " (" + rtrim(@ddirname) + ")" 676 print @rptline 677 678 select @rptline = space(4) + "Dump File Name:" + 679 space(9) + @cfilename 680 if (@cond_name != "Defaults" and @cfilename = "Default File Name") 681 select @rptline = @rptline + " (" + rtrim(@dfilename) + ")" 682 print @rptline 683 684 select @rptline = space(4) + "Estimated File Size:" + 685 space(4) + @cdumpsize 686 print @rptline 687 688 print "" 689 690 fetch c_display into 691 @cond_name, 692 @cond_cvalue, 693 @cmaxdumps, 694 @dumpcount, 695 @cpagecache, 696 @cproccache, 697 @cunused, 698 @chalt, 699 @cluster, 700 @cdumpsize, 701 @cfilename, 702 @cdirname 703 end 704 705 close c_display 706 707 /* 708 ** Get configured maximum number of dump conditions 709 */ 710 select @maxconds = value 711 from master.dbo.sysconfigures 712 where name = 'maximum dump conditions' 713 714 /* 715 ** Subtract 1 so that we don't count the defaults record 716 */ 717 select @curcount = (count(*) - 1) 718 from #conditions 719 720 /* 721 ** 18513, "Current number of conditions: %1!" 722 */ 723 exec sp_getmessage 18513, @print_str output 724 print @print_str, @curcount 725 /* 726 ** 18514, "Maximum number of conditions: %1!" 727 */ 728 exec sp_getmessage 18514, @print_str output 729 print @print_str, @maxconds 730 731 /* 732 ** Display CSMD-related configuration options. 733 */ 734 print "" 735 print "Configurable Shared Memory Dump Configuration Settings" 736 print "------------------------------------------------------" 737 738 select @cfgval = value 739 from master.dbo.sysconfigures 740 where name = 'dump on conditions' 741 742 /* 743 ** 19399, "Dump on conditions: %1!" 744 */ 745 exec sp_getmessage 19399, @print_str output 746 print @print_str, @cfgval 747 748 if (@cfgval = 0) 749 begin 750 /* 751 ** 19400, "You must run 'sp_configure 'dump on conditions',1' to 752 ** enable the shared memory dump facility." 753 */ 754 exec sp_getmessage 19400, @print_str output 755 print "" 756 print @print_str 757 print "" 758 end 759 760 select @cfgval = value 761 from master.dbo.sysconfigures 762 where name = 'number of dump threads' 763 764 /* 765 ** 19398, "Number of dump threads: %1!" 766 */ 767 exec sp_getmessage 19398, @print_str output 768 print @print_str, @cfgval 769 770 /* 771 ** Determine include errorlog setting. Set the @cfgval variable 772 ** to the default setting in the event there is no configuration row in 773 ** sysattributes. This must be the same default as in utils/shmdump.c. 774 */ 775 select @cfgval = 1 776 select @cfgval = int_value 777 from master.dbo.sysattributes 778 where class = @DUMPCOND_CLASS 779 and object_type = @OBJTYPE 780 and attribute = @CFG_ATTRIBUTE 781 and object = @CFG_ERRORLOG 782 783 /* 784 ** 19396, "Include errorlog in dump file: %1!" 785 */ 786 exec sp_getmessage 19396, @print_str output 787 print @print_str, @cfgval 788 789 /* 790 ** Determine merge files setting. Set the @cfgval variable 791 ** to the default setting in the event there is no configuration row in 792 ** sysattributes. This must be the same default as in utils/shmdump.c. 793 */ 794 select @cfgval = 1 795 select @cfgval = int_value 796 from master.dbo.sysattributes 797 where class = @DUMPCOND_CLASS 798 and object_type = @OBJTYPE 799 and attribute = @CFG_ATTRIBUTE 800 and object = @CFG_MERGE 801 802 /* 803 ** 19397, "Merge parallel files after dump: %1!" 804 */ 805 exec sp_getmessage 19397, @print_str output 806 print @print_str, @cfgval 807 808 809 /* 810 ** Calculate server memory usage 811 */ 812 813 select @cache_size = 0 814 select @proc_size = 0 815 816 817 818 /* 819 ** Obtain the current total memory allocated to the server. 104 is the 820 ** configuration # for total logical memory. 821 */ 822 select @memory = convert(int, comment) 823 from master.dbo.syscurconfigs 824 where config = 104 825 826 select @memory = (@memory / 1024) + 1 827 828 select @memory_str = convert(char(6), @memory) 829 select @position = charindex(" ", @memory_str) 830 select @memory_str = space(6 - @position) + rtrim(@memory_str) + " MB" 831 832 /* 833 ** 19 is the config # for named caches. 834 ** This value is in kilobytes. 835 ** For SDC server, ther are two kinds of data caches: 836 ** 1. Global cache 837 ** For this kind of cache, the instanceid in sysconfigures will be NULL. 838 ** 2. Private cache for an instance 839 ** For this kind of cache, the instanceid in sysconfigures will be the 840 ** instanceid of the instance on which the cache is configured. 841 ** So, for sysconfigures.instanceid, it might have below values: 842 ** (1) NULL 843 ** This means the cache is a global cache. 844 ** (2) instance id 845 ** This mean the cache is for instance. 846 ** 847 ** In this procedure, we referred the implementation in sp_helpcache. 848 ** First, we will insert all the caches including the global cache and 849 ** private cache for instances into a temporary table. Then, we will 850 ** scan every rows in this temporaty table. If a row's instanceid is NULL, 851 ** this means it is a global cache. In this case, we need to check if there 852 ** are private cache with the same name for the instance. If there is such 853 ** a cache, we need to skip this row to avoid duplicated calculation. 854 */ 855 856 select @cache_size = sum(cu.value) 857 from master.dbo.sysconfigures co, master.dbo.syscurconfigs cu 858 where co.config = 19 859 and co.config = cu.config 860 and co.name = cu.comment 861 862 863 select @cache_size_str = convert(char(6), (@cache_size / 1024) + 1) 864 select @position = charindex(" ", @cache_size_str) 865 select @cache_size_str = space(6 - @position) + rtrim(@cache_size_str) + " MB" 866 867 /* 868 ** Config # 146 is the procedure cache size value. 869 ** Config # 414 is the statement cache size value. 870 ** Since statement cache is added to the configured procedure cache 871 ** size, these two values must be combined to calculate the total 872 ** procedure cache size. 873 */ 874 select @proc_size = sum(convert(int, comment)) 875 from master.dbo.syscurconfigs 876 where config in (146, 414) 877 878 select @proc_size_str = convert(char(6), (@proc_size / 1024) + 1) 879 select @position = charindex(" ", @proc_size_str) 880 select @proc_size_str = space(6 - @position) + rtrim(@proc_size_str) + " MB" 881 882 select @shm_size_str = convert(char(6), @memory - (((@cache_size / 1024)) + (@proc_size / 1024))) 883 select @position = charindex(" ", @shm_size_str) 884 select @shm_size_str = space(6 - @position) + rtrim(@shm_size_str) + " MB" 885 886 887 888 print "" 889 print "Server Memory Allocation" 890 print "Procedure Cache Data Caches Server Memory Total Memory" 891 print "--------------- ----------- ------------- ------------" 892 print " %1! %2! %3! %4!", 893 @proc_size_str, @cache_size_str, @shm_size_str, @memory_str 894 895 896 897 898 if (@type = 'debug') 899 begin 900 print " " 901 /* 902 ** Printing Debug Information: 903 */ 904 exec sp_getmessage 18515, @print_str output 905 print @print_str 906 print " " 907 select @not_status = attrib_notify(@DUMPCOND_CLASS, 1, @OBJTYPE, 908 1, NULL, 909 NULL, NULL, NULL, NULL, 910 NULL, NULL, NULL, NULL, 911 @ATTR_FETCH) 912 end 913 914 /* 915 ** Indicate success 916 */ 917 return 0 918 919 /* 920 ** End sp_shmdumpdisp 921 */ 922
exec sp_procxmode 'sp_shmdumpdisp', 'AnyMode' go Grant Execute on sp_shmdumpdisp to public go
| DEPENDENCIES |
| PROCS AND TABLES USED reads table master..sysconfigures (1)  reads table master..sysmessages (1) read_writes table tempdb..#attname (1) reads table master..syscurconfigs (1) calls proc sybsystemprocs..sp_getmessage  reads table master..sysmessages (1) calls proc sybsystemprocs..sp_validlang reads table master..syslanguages (1) reads table sybsystemprocs..sysusermessages reads table master..syslanguages (1) calls proc sybsystemprocs..sp_shmdumpsize reads table master..sysconfigures (1) reads table master..sysattributes (1) reads table master..syscurconfigs (1) read_writes table tempdb..#conditions (1) reads table master..sysattributes (1) CALLERS called by proc sybsystemprocs..sp_shmdumpconfig |
