Increases the amount of space allocated to a database, as well as to the modified pages section of an archive database.
alter database database_name
[on {default | database_device} [= size]
[, database_device [= size]]...]
[log on {default | database_device} [= size]
[, database_device [= size]]...]
set { [durability = { no_recovery | at_shutdown | full}]
[[,] dml_logging = {full | minimal} ]
[[,] template = { database_name | NULL}]}
[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 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 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.
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. 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 |
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.
determines the durability level of the database, and is one of:
full – all transactions are written to disk. This is the default if you do not specify a durability level when you create the database, and ensures full recovery from a server failure. All system databases except tempdb use this durability level (the traditional durability level for disk-resident databases). tempdb uses a durability level of no_recovery.
no_recovery – transactions are durable only while the server is running. All durability is lost if the server fails or is shut down politely. For disk-residents databases with durability set to no_recovery, Adaptive Server periodically writes data at runtime to the disk devices, but in an uncontrolled manner. After any shutdown (polite, impolite, or server failure and restart) a database created with no_recovery is not recovered, but is re-created from the model or, if defined, the template database.
at_shutdown – transactions are durable while the server is running and after a polite shutdown. All durability is lost if the server fails.
indicates the level of logging for insert, update, and delete commands. full (the default) records all changes to the log for a complete record of all transactions. If the database uses minimal logging, Adaptive Server attempts to not log row or page changes to syslogs. However, Adaptive Server may generate some in-memory logging activity to support run-time operations such as rolling back transactions.
determines the template the database uses. One of:
database_name – user database that is disk-resident at full durability, and in a usable state.
NULL – removes the binding to the current template database. The database uses model as its template database during subsequent server restarts.
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 resynchronization of proxy tables within the proxy database.
Adds 3MB (1536 pages) to a user database configured for 2K logical pages 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 for a server configured for 2K logical pages:
alter database production on userdata1 = "10M" log on logdev = '2.5m'
Changes the durability level of pubs5_rddb, a relaxed-durability database to change it to a regular database with full durability:
alter database pubs5_rddb set durability = full
Alters the template for the pubs3 database:
alter database pubs3 set template = new_pubs_template_db
Changes the durability level of a disk-resident database with relaxed durability:
alter database pubs7 set durability=at_shutdown
Changes the DML logging level for the model database, which is set to a durability level of full. Any databases created from model after this change inherit the minimal logging level property:
alter database model set dml_logging = minimal
You can use alter database to add space to the modified pages section of the archive database at any time, not only when space runs out. Increasing the space in the modified pages section allows a suspended command to resume operation. The syntax is:
alter database database_name [ on database_device [= size] [, database_device [= size]]...]
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.
You cannot set specify model, master or sybsystemdb as the template database.
Setting the database name in the use template claue to NULL removes the binding to any existing template database, and defines model as the template database.
Altering the template definition of a database that appears earlier in the database recovery order sequence than its template database automatically reorders the recovery order so the new template database appears prior to its dependent database in the database recovery order when you restart the server.
If you change the settings for durability or dml_logging, alter database automatically attempts to set the databases to single-user mode before executing the command. You can manually set the database to single-user mode before you issue alter table.
Databases must be in single-user mode before you can change the durability level setting.
You can increase the size of an in-memory database only on in-memory storage caches that already host the in-memory database.
You cannot change the durability level of system, template, or local temporary databases.
The load sequence is broken when you change the durability level of the database to full. For example, for a disk-resident database using full durability, if you:
Dump a database.
Perform a dump transaction.
Perform a second dump transaction.
Changed the durability to no_recovery.
Changed the durability to full.
You cannot perform a third dump transaction. Instead, you must perform a full dump database.
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.
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.
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 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.
ANSI SQL – Compliance level: Transact-SQL extension.
alter database permission defaults to the database owner. System administrators can also alter databases. Only the database owner or a login with sa_role can use alter database to change the database-wide logging setting.
Values in event and extrainfo columns of sysaudits are:
Event |
Audit option |
Command or access audited |
Information in extrainfo |
|---|---|---|---|
2 |
alter |
alter database |
|
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
