The sysaltusages Table and the Scratch Database

sysaltusages is a data-only-locked table that maps page numbers in an archive database to the actual page within either the database dump and its stripes, or the modified pages section.

However, unlike the sysusages table in a traditional database, sysaltusages does not map every logical page in the database, it maps only:

  • Pages that have been stored in a database dump

  • Pages that have been modified, and therefore, relocated to the modified pages section

See the Reference Manual: Tables.

Note: Because sysaltusages is a row-locked catalog, you may need to periodically use reorg to reclaim logically deleted space.

The scratch database stores the sysaltusages table. The scratch database is used to provide flexibility as to where the sysaltusages table is located.

The scratch database can be any database (with some exceptions like master and temporary databases). SAP recommends that you use a dedicated database as the scratch database, because:

  • The size of sysaltusages may vary, depending on the number of archive databases it supports. You cannot decrease the size of a database, but if it is too large, you can drop it and re-create a smaller database when required.

  • It allows you to turn on trunc log on checkpoint so that you can automatically truncate the database log.

Apart from hosting the sysaltusages table, this database is like any other. You can use threshold procedures and other space management mechanisms to manage space within the database.

To specify a database that is a scratch database, enter
sp_dboption <db name>, "scratch database", "true"

Each archive database can be assigned to only one scratch database at a time, but multiple archive databases can use the same scratch database. If you have a large number of archive databases, you may want to define multiple scratch databases.