Shrinking a Database

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']

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

where:

off – specifies the device names from which you are releasing space. The off clause includes:
- database_device – is the name of the database device on which to locate the database extension. A database can occupy more than one database device with different amounts of space on each device.
  database_device includes either the =size parameter or the from or to parameters (not both). The default from page is 0 (zero). If you do not specify any modifiers with the from clause, Adaptive Server releases all storage for this database on the indicated device.
- size – specifies the amount of space to be released from the database. alter database accepts a specific size, or a from ... to declaration:
  - Specific size – an integer. If you do not include a unit specifier, Adaptive Server uses megabytes as the unit. You can also include “k” or “K” (kilobytes), “m” or “M” (megabytes), “g” or “G” (gigabytes), and “t” or “T” (terabytes). Sybase recommends that you always include a unit specifier. Quotes are optional if you do not include a unit specifier. However, you must use quotes if you include a unit specifier. If you do not provide a unit specifier, the value provided is presumed to be in megabytes. For example:
    - alter database ... off mydev1 = 200 – removes 200 megabytes
    - alter database ... off mydev1 = '2g' – removes 2 gigabytes
  - from ... to declaration – unsigned integers. See below.
  Adaptive Server releases the highest-numbered logical pages of this database associated with the indicated device. Adaptive Server rounds the value for size up, if needed, to indicate an even number of allocation units.
  
  If you specify more space than is currently in use by this database on the indicated device, Adaptive Server limits the specified size or page IDs to the amount of space available.
- from page_number – specifies the low end of the range of logical pages to be released. Adaptive Server rounds the from value down, if needed, to represent the first page of the allocation unit containing the indicated page. The default value for from is 0 (zero).
- to page_number – specifies the high end of the range of logical pages to be released. Adaptive Server rounds the to value up, if necessary, to represent the last page of the allocation unit containing the indicated page. The default value for to is the highest-numbered logical page in the database.
  However, if the to page is an allocation page itself (which is the starting page of an allocation unit) and is not the same as the from page, the alter database ... off command does not affect the allocation unit containing the specified to page. Adaptive Server assumes that a user requesting a precise page range does not intend to affect the specified end page, so it decrements the end page rather than increasing it.
  
  Note: Use either alter database off or alter database log off to shrink the log segment. Both commands perform the same function.
with time='time' – specifies the maximum length of time alter database ... off may run, and is specified as hours, minutes, and seconds (HH:MM:SS). If you do not include the full specification, alter database interprets the missing sections for time as:
- XX:YY is interpreted as MM:SS.
- XX is interpreted as minutes.
- Empty sections are treated as zero. For example, alter database interprets both “::30” and “:30” as 30 seconds. However, alter database interprets “1::” as 1 hour, and "2:" as 2 minutes.
  If alter database ... off does not finish within the specified period of time, work in progress is abandoned, and the command exits with an error message. Any completed work remains done.

Note: Adaptive Server does not issue an error message if you indicate a from page value that is greater than the to page value. Instead, Adaptive Server swaps the from and to values before applying the rounding logic.

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 Adaptive Server.