2414702 - Tempdb system tables space continues to grow and has not been cleared out - SAP ASE | SAP Knowledge Base Article

SAP Knowledge Base Article - Preview

2414702 - Tempdb system tables space continues to grow and has not been cleared out - SAP ASE

Symptom

  • The size of indexes for tempdb system table continue to grow.

  • optdiag output show large number of "Empty leaf page count".
    For example:
    Specified database:                     "tempdb"
    Specified table owner:                 not specified
    Specified table:                           "syspartitions"
    Specified column:                        not specified

    Table owner:                              "dbo"
    Table name:                               "syspartitions"

    Statistics for table:                      "syspartitions"
          Data page count:                   1095
          Empty data page count:          0
          Data row count:                     10561.0000000000000000
          Forwarded row count:             0.0000000000000000
          Deleted row count:                 1888.0000000000000000
          Data page CR count:               1.0000000000000000
          OAM + allocation page count:  699
          First extent data pages:           200
          Data row size:                        80.0289855072463752
          Pages in largest partition:        1095

     Derived statistics:
          Data page cluster ratio:           0.9999900000000000
          Space utilization:                    0.3855441891177448
          Large I/O efficiency:               0.9999300048996574

    Statistics for index:                   "csyspartitions" (clustered)
     Index column list:                      "id", "indid", "partitionid"
          Leaf count:                               1786
          Empty data page count:             136
          Data page CR count:                  6567.0000000000000000
          Index page CR count:                1759.0000000000000000
          Data row CR count:                   7709.0000000000000000
          First extent leaf pages:              155
          Leaf row size:                           17.0000000000000000
          Index height:                            2

    Statistics for index:                   "ncsyspartitions" (nonclustered)
     Index column list:                      "partitionid", "indid"
          Leaf count:                              1756
          Empty leaf page count:             1195
          Data page CR count:                7511.0000000000000000
          Index page CR count:               1689.0000000000000000
          Data row CR count:                  8643.0000000000000000
          First extent leaf pages:             166
          Leaf row size:                          13.0000000000000000
          Index height:                           2

    Statistics for index:                   "nc2syspartitions" (nonclustered)
     Index column list:                      "id", "indid", "name"
          Leaf count:                              4862
          Empty leaf page count:             48892
          Data page CR count:                20582.0000000000000000
          Index page CR count:               3328.0000000000000000
          Data row CR count:                  24333.0000000000000000
          First extent leaf pages:             203
          Leaf row size:                          28.5942028985507264
          Index height:                           2


Read more...

Environment

  • Adaptive Server Enterprise - ASE 15.7
  • Adaptive Server Enterprise - ASE 16.0

Product

SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0

Keywords

1105, tempdb, reorg reclaim_space , KBA , BC-SYB-ASE , Sybase ASE Database Platform (non Business Suite) , Product Enhancement

About this page

This is a preview of a SAP Knowledge Base Article. Click more to access the full version on SAP ONE Support launchpad (Login required).

Search for additional results

Visit SAP Support Portal's SAP Notes and KBA Search.