SAP Knowledge Base Article - Preview

2414702 - Tempdb system tables and indexes continues to grow and are not reduced in size - SAP ASE

Symptom

  • The size of system tables in the system tempdb or a user tempdb continues to grow and will not decrease when (nearly) empty
  • The space in use of a tempdb does not drop below a certain level

  • Large size of index for system tables in tempdb, even when table is empty or has few rows
  • This can be shown in any 'space checking' output run on tempdb system tables, for example sp_spaceused and optdiag
    • The examples show one table, but it can be seen on all tables and particularly ones that are involved in managing objects

    • Output from sp_spaceused shows large index sizes:

sp_spaceused syspartitions, 1

index_name           size                reserved
----------------------    ----------------   -----------------
csyspartitions                196 KB          1128 KB
ncsyspartitions        586824 KB      587944 KB
nc2syspartitions    2647056 KB    2647944 KB

  • Optdiag output shows large number of "Empty leaf page count" pages:

    Specified database:                     "tempdb_1"
    ...
    Table name:                               "syspartitions"

    Statistics for index:                   "csyspartitions" (clustered)
     Index column list:                      "id", "indid", "partitionid"
          Leaf count:                               1786
          Empty data page count:             136

    Statistics for index:                   "ncsyspartitions" (nonclustered)
     Index column list:                      "partitionid", "indid"
          Leaf count:                              1756
          Empty leaf page count:             1195      

    Statistics for index:                   "nc2syspartitions" (nonclustered)
     Index column list:                      "id", "indid", "name"
          Leaf count:                              4862
          Empty leaf page count:             48892


Read more...

Environment

  • SAP Adaptive Server Enterprise (ASE)  ASE 15.7 & 16.0

Product

SAP Adaptive Server Enterprise 15.7 ; SAP Adaptive Server Enterprise 16.0

Keywords

1105, tempdb, reorg reclaim_space , csyscolumns , syscolumns , csyscomments , syscomments, csysindexes , sysindexes . csysstatistics , sysstatistics , csystabstats  , systabstats , nc2syspartitions   , syspartitions , ncsysobjects  , sysobjects , ncsyspartitions , syspartitions , 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 for Me (Login required).

Search for additional results

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