Reference Manual: Commands

Home

Reference Manual: Commands

Chapter 1 Commands

alter database

Usage

Restrictions

alter database for proxy update drops all proxy tables in the proxy database.
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.

Backing up master after allocating more space

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.

Placing the log on a separate device

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.

Getting help on space usage

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

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.

Using alter database to awaken sleeping processes

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.

Using for proxy_update

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.


	Expand Search