ALTER DATABASE Statement

Increases the amount of disk space allocated to a database.

Syntax

ALTER DATABASE database_name
[ON {DEFAULT | database_device} [= size]
[, database_device [= size]]...]
[FOR LOAD]

where

database_name

Is the database whose storage size is to be changed.

ON

Indicates a size and/or location for the database extension. The default extension size is 1 megabyte (MB).

If extending the amount of space allocated to the transaction log, give the name of the log's database device. This will automatically allocate new space only for the transaction log if the database was originally created using the LOG ON clause.

If the database was not created with the LOG ON clause and you want to move the log to a newly added device, then, after altering the database, use the sp_logdevice system stored procedure to make the newly allocated space available only for the log.

Note You must use the sp_logdevice system stored procedure or the LOG ON extension of CREATE DATABASE to put your transaction log on a separate device before you can use the DUMP TRANSACTION statement. Placing the log on the same device as the database minimizes the number of recoverability features available for your database.

DEFAULT

Indicates that ALTER DATABASE can put the database extension on any default database device(s). To see a listing of all available database devices, use the sp_helpdevice system stored procedure. To specify a size for the database extension without specifying the location, use ON DEFAULT = size. To change the status of a database device to default, use the sp_diskdefault system stored procedure.

database_device

Is the logical name of the database device on which you want to place the database extension. A database can reside on more than one database device with different amounts of space on each. To create a device, use the DISK INIT statement.

size

Specifies the amount of space, in megabytes, allocated to the database extension. The minimum, and default, extension is 1 MB (512 2K pages). Legal values range from 1 through 2^24 MB. If SQL Server can't allocate the requested size, it allocates as much space as possible, in 0.5-MB units (allocation units).

When an explicit value is not supplied, SQL Server will use the larger value between the size of the model database or the configured database size (configured with sp_configure). For example, if database size is configured to 2 (the default) and the model database has been altered to 3MB, the database will be 3MB.

FOR LOAD

Prevents use of the database until after a successful database load. This can be used only if the database was created with the FOR LOAD option. For details, see the CREATE DATABASE statement.

Remarks

ALTER DATABASE increases the size of a database. To rename a database, use the sp_renamedb system stored procedure. To decrease the size of a database, see the DBCC statement's SHRINKDB option.

It is important to back up the master database using the DUMP DATABASE statement after each use of ALTER DATABASE. This makes recovery easier and safer if the master database becomes damaged. (If you use ALTER DATABASE and fail to back up the master database, you might still be able to recover the changes with the DISK REFIT statement. For details, see the DISK REFIT statement.)

The SYSTEM and DEFAULT segments are mapped to each new database device included in an ALTER DATABASE statement. When ALTER DATABASE extends a database on a device already in use by that database, the segments mapped to that device are also extended. The maximum number of segments for any database is 32.

Permission

ALTER DATABASE permission defaults to the database owner if he or she has CREATE DATABASE permission. Permission cannot be transferred. The system administrator can also alter databases.

Examples

A.    Alter a Single Database on a Single Device

This example alters the testing database on the DEVICE1 device by adding an 8-MB fragment.

ALTER DATABASE testing
    ON DEVICE1 = 8

B.    Alter a Database's Log Space

This example increases the size of the sales_info database by 5 MB. After the database has been altered, that fragment is made log-only by executing the sp_logdevice system stored procedure.

ALTER DATABASE sales_info
    ON DEVICE9 = 5
go
sp_logdevice sales_info, DEVICE9

See Also