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: |
|---|---|
2K |
1MB |
4K |
1MB |
8K |
2MB |
16K |
4MB |
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.
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.
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.
forces the re-synchronization of proxy tables within the proxy database.
Adds 1MB to the database mydb on a default database device:
alter database mydb
Adds 3MB to the space allocated for the pubs2 database on the database device named newdata:
alter database pubs2 on newdata = 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
Quotes are optional if you do not include a unit specifier. However, you must use quotes if you include a unit specifier.
If you do not include a unit specifier, Adaptive Server interprets the size in terms of megabytes of disk space, and this number is converted to the logical page size the server uses.
Adaptive Server reports an error if the total size of all fixed-length columns, plus the row overhead, is greater than the table’s locking scheme and page size allows.
If you create a data-ony locking (DOL) table with a variable-length column that exceeds a 8191-byte offset, you cannot add any rows to the column.
Because Adaptive Server allocates space for databases for create database and alter database in chunks of 256 logical pages, these commands round the specified size down to the nearest multiple of allocation units.
You can specify the size as a float datatype, however, the size is rounded down to the nearest multiple of the allocation unit.
The minimum size that space is allocated to a database is the larger of:
One megabyte.
One allocation unit of the server’s logical page size.
Although Adaptive Server does create tables in the following circumstances, you will receive errors about size limitations when you perform data manipulation language operations:
If the total row size for rows with variable-length columns exceeds the maximum column size.
If the length of a single variable-length column exceeds the maximum column size.
For DOL tables, if the offset of any variable-length column other than the initial column exceeds the limit of 8191 bytes.
If Adaptive Server cannot allocate the requested space, it comes as close as possible per device and prints a message telling how much space has been allocated on each database device.
You must be using the master database, or executing a stored procedure in the master database, to use alter database.
If Adaptive Server cannot allocate the requested space, it comes as close as possible per device and prints a message telling how much space has been allocated on each database device.
You can expand the master database only on the master device. An attempt to use alter database to expand the master database to any other database device results in an error message. Here is an example of the correct statement for modifying the master database on the master device:
alter database master on master = 1
Each time you allocate space on a database device with create database or alter database, that allocation represents a device fragment, and the allocation is entered as a row in sysusages.
If you use alter database on a database that is in the process of being dumped, the alter database command cannot complete until the dump finishes. Adaptive Server locks the in-memory map of database space use during a dump. If you issue an alter database command while this in-memory map is locked, Adaptive Server updates the map from the disk after the dump completes. If you interrupt alter database, Adaptive Server instructs you to run sp_dbremap. If you fail to run sp_dbremap, the space you added does not become available to Adaptive Server until the next reboot.
You can use alter database on database_device on an offline database.
Back up the master database with the dump database command after each use of alter database. This makes recovery easier and safer in case master becomes damaged.
If you use alter database and fail to back up master, you may be able to recover the changes with disk refit.
To increase the amount of storage space allocated for the transaction log when you have used the log on extension to create database, give the name of the log’s device in the log on clause when you issue the alter database command.
If you did not use the log on extension of create database to place your logs on a separate device, you may not be able to recover fully in case of a hard disk crash. In this case, you can extend your logs by using alter database with the log on clause, then using sp_logdevice.
To see the names, sizes, and usage of device fragments already in use by a database, execute sp_helpdb dbname.
To see how much space the current database is using, execute sp_spaceused.
The system and default segments are mapped to each new database device included in the on clause of an alter database command. To unmap these segments, use sp_dropsegment.
When you use alter database (without override) to extend a database on a device already in use by that database, the segments mapped to that device are also extended. If you use the override clause, all device fragments named in the on clause become system/default segments, and all device fragments named in the log on clause become log segments.
If user processes are suspended because they have reached a last-chance threshold on a log segment, use alter database to add space to the log segment. The processes awaken when the amount of free space exceeds the last-chance threshold.
If the for proxy_update clause is entered with no other options, the size of the database will not be extended; instead, the proxy tables, if any, will be dropped from the proxy database and re-created from the metadata obtained from the pathname specified during create database ... with default_location = ‘pathname’.
If this command is used with other options to extend the size of the database, the proxy table synchronization is performed after the size extensions are made.
The purpose of this alter database extension is to provide the DBA with an easy-to-use, single-step operation with which to obtain an accurate and up-to-date proxy representation of all tables at a single remote site.
This re-synchronization is supported for all external data sources, and not just the primary server in a HA-cluster environment. Also, a database need not have been created with the for proxy_update clause. If a default storage location has been specified, either through the create database command or with sp_defaultloc, the metadata contained within the database can be synchronized with the metadata at the remote storage location.
To make sure databases are synchronized correctly so that all the proxy tables have the correct schema to the content of the primary database you just reloaded, you may need to run the for proxy_update clause on the server hosting the proxy database.
SQL92 – Compliance level: Transact-SQL extension.
alter database permission defaults to the Database Owner. System Administrators can also alter databases.
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
