To extend a database, and to specify where storage space is to be added, use the full alter database syntax:
alter database database_name
[on {default | database_device} [= size]
[, database_device [= size]]...]
[log on {default | database_device} [= size]
[, database_device [= size]]...]
[with override]
[for load]
[for proxy_update]
In its simplest form, alter database adds one logical page 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.
To add logical page 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.
To add 3MB to the space allocated for the newpubs database on the database device named pubsdata1, enter:
alter database newpubs on pubsdata1 = "3M"
If Adaptive Server cannot allocate the requested size, it allocates as much as it can on each database device, with a minimum allocation of .5MB (256 2K 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.
The following 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
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. See Chapter 28, “Backing Up and Restoring User Databases,” for a discussion of duplicating space allocation when loading a dump into a new database.
