alter database syntax

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

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.

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

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

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.