alter database


Increases the amount of space allocated to a database.


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]



is the name of the database. The database name can be a literal, a variable, or a stored procedure parameter.


indicates a size and/or location for the database extension. If you have your log and data on separate device fragments, use this clause for the data device and the log on clause for the log device.


indicates that alter database can put the database extension on any default database device(s) (as shown by sp_helpdevice). To specify a size for the database extension without specifying the exact location, use this command:

on default = size

To change a database device’s status to default, use the system procedure sp_diskdefault.


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. Add database devices to Adaptive Server with disk init.


is the amount of space to allocate to the database extension. size can be in the following unit specifiers: ‘k’ or ‘K’ (kilobytes), ‘m’ or ‘M’ (megabytes), and ‘g’ or ‘G’ (gigabytes). Sybase recommends that you always include a unit specifier. If you do not specify a value, alter database extends a database by 1MB or 4 allocation unit, whichever is larger. The following table describes the minimum amounts:

Server’s logical page size

Database extended by:









log on

indicates that you want to specify additional space for the database’s transaction logs. The log on clause uses the same defaults as the on clause.

with override

forces Adaptive Server to accept your device specifications, even if they mix data and transaction logs on the same device, thereby endangering up-to-the-minute recoverability for your database. If you attempt to mix log and data on the same device without using this clause, the alter database command fails. If you mix log and data, and use with override, you are warned, but the command succeeds.

for load

is used only after create database for load, when you must re-create the space allocations and segment usage of the database being loaded from a dump.

for proxy_update

forces the re-synchronization of proxy tables within the proxy database.


Example 1

Adds 1MB to the database mydb on a default database device:

alter database mydb

Example 2

Adds 3MB to the space allocated for the pubs2 database on the database device named newdata:

alter database pubs2 
on newdata = 3

Example 3

Adds 10MB of space for data on userdata1 and 2MB for the log on logdev:

alter database production 
on userdata1 = 10
log on logdev = 2



Backing up master after allocating more space

Placing the log on a separate device

Getting help on space usage

The system and default segments

Using alter database to awaken sleeping processes

Using for proxy_update


SQL92 – Compliance level: Transact-SQL extension.


alter database permission defaults to the Database Owner. System Administrators can also alter databases.

See also

Commands create database, disk init, drop database, load database

System procedures sp_addsegment, sp_dropsegment, sp_helpdb, sp_helpsegment, sp_logdevice, sp_renamedb, sp_spaceused