Chapter 1: Commands

alter database

Description

Increases the amount of space allocated to a 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]

Parameters

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

on: indicates a size and 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.

default

indicates that alter database can put the database extension on any default database devices (as shown by the sp_helpdevice stored procedure in Chapter 1, “System Procedures,” in Reference Manual: Procedures). To specify a size for the database extension without specifying the exact location, use:

on default = size

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

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

size

is the amount of space to allocate to the database extension. The following are example unit specifiers, using uppercase, lowecase, single and double quotes interchangeably: ‘k’ or “K” (kilobytes), “m” or ‘M’ (megabytes), “g” or “G” (gigabytes), and ‘t’ or ‘T’ (terabytes). Sybase recommends that you always include a unit specifier. 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 provide a unit specifier, the value provided is presumed to be in megabytes.

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

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 resynchronization of proxy tables within the proxy database.

Examples

Example 1

Adds 1MB to a 2K-page-size 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 = "10M"
log on logdev = '2.5m'

Usage

Restrictions

Quotes are optional if you do not include a unit specifier. However, you must use quotes if you include a unit specifier.
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.
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.
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 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.
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 do not run sp_dbremap, the space you added does not become available to Adaptive Server until the next time you restart the server.
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 dump database after each use of alter database. This makes recovery easier and safer in case master becomes damaged.
If you use alter database and do not 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 to move the log to its own devices.

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 is not extended; instead, the proxy tables, if any, are dropped from the proxy database and re-created from the metadata obtained from the path name 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 Database Administrator 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.
Resynchronization is supported for all external data sources, 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.

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Auditing

Values in event and extrainfo columns of sysaudits are:

Event	Audit option	Command or access audited	Information in extrainfo
2	alter	alter database	Roles – Current active roles Keywords or options – alter size Previous value – NULL Current value – NULL Other information – NULL Proxy information – Original login name, if a set proxy is in effect