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

• 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

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

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