Shrinking Databases

SAP ASE allows you to shrink databases, freeing unused space for reuse or deletion.

If SAP ASE encounters data on the portion of the database you are shrinking, that data is moved to a new location before the space is removed from the database. Once the portions to be removed are empty, the physical storage is replaced by references to a null device, making the freed space available for reuse or deletion.

Generally, you can shrink databases that are online and in use. However, in some situations, you must put SAP ASE in single user mode. See "Restrictions."

• Shrinking a Database
  Shrinking a database on a device reduces the amount of free space for all segments on that device.
• How SAP ASE Shrinks the Database
  For database shrink operations that do not include pages 0 – 255, SAP ASE clears the logical pages and removes them from physical storage. Any data the logical pages contain is moved to other newly allocated pages in the same database.
• Shrink Operations on Databases That Contain Text or Image Data
  Shrink operations require all text and image columns to have backlinking pointers to the home data row in its first text page.
• Restarting Partially Completed Shrink Operations
  Reissuing the same alter database command to shrink a database automatically restarts earlier partially completed shrink database operations, resuming the operation from the last completed allocation unit.
• Moving Data Before Shrinking the Database
  Shrinking a database involves removing physical storage from it without losing the data in that storage.
• Determine the Status of a Shrink Operation
  Use the shrinkdb_status function to determine the status of a shrink operation.
• Upgrading or Downgrading Shrunken Databases
  Upgraded databases may contain replicated tables that are initially setup with sp_reptostandby 'use index', which creates indexes on text and image off-row columns. However, dbcc shrinkdb_setup marks these replication indexes as suspect.
• Restrictions
  There are several restrictions that apply to shrink operations on databases.