Using alter database

Use alter database to extend a database, and specify where storage space is to be added.

In its simplest form, alter database adds the configured default amount of space from the default database devices. If your database separates log and data, the space you add is used only for data. Use sp_helpdevice to find names of database devices that are in your default list.

See the Reference Manual: Commands.

To add space from a default database device to the newpubs database, enter:

alter database newpubs

The on and log on clauses operate like the corresponding clauses in create database. You can specify space on a default database device or some other database device, and you can name more than one database device. If you use alter database to extend the master database, you can extend it only on the master device. The minimum increase you can specify is 1MB or one allocation unit, whichever is larger.

If SAP ASE cannot allocate the requested size, it allocates as much as it can on each database device, with a minimum allocation of 256 logical pages per device. When alter database completes, it prints messages telling you how much space it allocated; for example:

Extending database by 1536 pages on disk pubsdata1

Check all messages to make sure the requested amount of space was added.

This command adds 2MB to the space allocated for newpubs on pubsdata1, 3MB on a new device, pubsdata2, and 1MB for the log on tranlog:

alter database newpubs 
on pubsdata1 = "2M", pubsdata2 =" 3M"
log on tranlog

Note: Each time you issue the alter database command, dump the master database.

Use with override to create a device fragment containing log space on a device that already contains data or a data fragment on a device already in use for the log. Use this option only when you have no other storage options and when up-to-the-minute recoverability is not critical.

Use for load only after using create database for load to re-create the space allocation of the database being loaded into the new database from a dump.