Shrinking a Database

Shrinking a database on a device reduces the amount of free space for all segments on that device.

Use the alter database command to shrink databases. The syntax is:

alter database database_name
. . . 
     off database_device {=size  | [from page_number] [to page_number]}
     [, database_device…]
     [with timeout='time']
     [with check_only]

See Reference Manual: Commands for complete syntax and parameter descriptions.

The examples in this section are based the mydb database, which is originally configured as:

create database mydb on datadev1 = ‘2G’ log on logdev1 = ‘250M’
alter database mydb on old_dev =‘2G’

This example shrinks mydb to remove the old data (datadev1 must have sufficient space to receive all data moved from old_dev):

alter database mydb off old_dev

Any data presently on old_dev migrates to new_dev, and the mydb database no longer includes the old_dev device. The space old_dev occupied is now a data hole (that is, the logical pages exist, but they no longer include storage). If necessary, you can now drop the old_dev device.

In this example, mydb was extended onto a shared device (in two steps, as the need for more space arose):

create database mydb on datadev1 = ‘2G’, shared_dev = ‘2G’ log on logdev1 = ‘250M’
alter database mydb on shared_dev = ‘300M’
alter database mydb on shared_dev = ‘200M’

To remove the 500MB of space that is no longer required:

alter database mydb off shared_dev = ‘500M’

The clause ='500M' removes the last 500 megabytes added because that space occupies mydb's highest-numbered logical pages on the shared_dev device, but does not touch the original 2 gigabytes.

This example releases the final 500 megabytes from the first disk piece occupied by shared_dev.

In this example, the data still required resides on the 500 megabytes added to shared_dev, but the original 2G of shared_dev is nearly empty. The server is configured for 8k logical pages, and mydb is described in sysusages as:

datadev1 uses logical pages 0 – 262143
shared_dev uses logical pages 262144 – 524287
logdev1 uses logical pages 524288 – 556287
shared_dev uses logical pages 556288 – 594687
shared_dev uses logical pages 594688 – 620287

It is much faster to release the empty space than to release space that contains data. To release the final 500 megabytes from the first disk piece occupied by shared_dev, you must determine the page range. Because 500 megabytes on an 8k page represents 64000 logical pages, subtract this value from the start of the disk piece that comes after the one you want to release (in this case logdev1):

524288 - 64000 = 460288

So, 460288 is the page number for the first page to release.

You must run alter database ... off with the database in single user mode to release part of a disk piece.

 sp_dboption mydb, ‘single user’, true

See "Restrictions."

To release the final 500 megabytes from the first disk piece occupied by shared_dev:

alter database mydb off shared_dev from 460288 to 524288

Disable single-user mode:

sp_dboption mydb, ‘single user’, false

This example removes the old_dev device (described in example 1), but performs the work 10 minutes at a time, allowing you to use that connection for other work:

 alter database mydb off old_dev with time=’10:00’

Repeat this command until the device is removed (you can perform other work in between iterations).

Note: alter database ... off does not halt after exactly 10 minutes if the command is not finished. Instead, the command works for 10 minutes, then stops at the next stable stopping point (that is, when the allocation unit it is currently working on is released). An allocation unit, a group of 256 pages, is the smallest configurable unit of database storage within SAP ASE.