alter database

Description

Increases the amount of space allocated to a database, as well as to the modified pages section of an archived database. Alters one or more database-wide properties such as DML logging level, defaults for compression, in-row large object (LOB) storage, and so on.

Syntax

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}]
	[, [no] async_init]
	[, compression = {none | row | page}]
	[, lob_compression = {compression_level | off}]
	[,] inrow_lob_length = value [log off database_device
		[= size | [from logical_page_number] [to logical_page_number]]
	[, database_device
	[= size | [from logical_page_number] [to logical_page_number]]
	[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 on for the data device and log on 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 “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. Use disk init to add database devices to Adaptive Server®.

size

is the amount of space to allocate to the database extension. The following are example unit specifiers, using uppercase, lowercase, 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. Minimum amounts are:

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.

durability

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.

[no] async_init

Enables or disables asynchronous database initialization.

compression

indicates the level of compression alter database applies to newly created tables in this database.

  • none – the data is not compressed.

  • row – compresses one or more data items in an individual row. Adaptive Server stores data in a row-compressed form only if the compressed form saves space compared to an uncompressed form.

  • page – when the page fills, existing data rows that are row-compressed are then compressed using page-level compression to create page-level dictionary, index, and character-encoding entries.

    Adaptive Server compresses data at the page level only after it has compressed data at the row level, so setting the compression to page implies both page and row compression.

lob_compression = off | compression_level

Determines the compression level for the newly created table. Selecting off means the table does not use LOB compression.

compression_level

Table compression level:

  • 0 – the lob column is not compressed.

  • 1 through 9 – Adaptive Server uses ZLib compression. Generally, the higher the compression number, the more Adaptive Server compresses the LOB data, and the greater the ratio between compressed and uncompressed data (that is the greater the amount of space savings, in bytes, for the compressed data versus the size of the uncompressed data).

    However, the amount of compression depends on the LOB content, and the higher the compression level , the more CPU-intensive the process. That is, level 9 provides the highest compression ratio but also the heaviest CPU usage.

  • 100 – Adaptive Server uses FastLZ compression. The compression ratio that uses the least CPU usage; generally used for shorter data.

  • 101 – Adaptive Server uses FastLZ compression. A value of 101 uses slightly more CPU than a value of 100, but uses a better compression ratio than a value of 100.

inrow_lob_length = value

specifies the number of bytes for an in-row text or image LOB column, and the number of characters for an in-row Unitext LOB column.

log off database_device

removes unwanted portions of the log for the database from the specified database device. Using log off decreases the amount of space allocated to the log of a database, as well as to the modified pages section of an archive database.

You cannot use log off with any other alter database parameter, including log on, for load, or with override.

= size

specifies the amount of space at the end of the device that the command should affect. For this purpose, the end of a device is the highest-numbered logical page used by this database on that device. This command specifies physical storage, removing every log page on the specified device from the database:

alter database sales_db log off mylogdev

Size specifications round up to fit an exact number of allocation units. In an installation using a 16KB logical page size, you remove 52MB, not 50, because each allocation unit on that server is 4MB. The only time Adaptive Server removes less space than specified is when the database’s log segment uses less than that much space on that device.

from logical_page_number

identifies the first page number affected by this command. Adaptive Server automatically adjusts the number to indicate the allocation page’s page ID. The default from location is the lowest numbered logical page on the device.

to logical_page_number

identifies the last page affected by the command. Only complete allocation units (multiples of 256 pages) can be removed, so Adaptive Server automatically adjusts the page number upwards to the last page in the allocation unit. If logical_page_number is the exact page number of an allocation page (that is, it is divisible by 256), that allocation unit is unaffected. For example, to 512 affects pages up to but not including page 512.

The default to location is the highest-numbered logical page on the device.

dml_logging {minimal | default}

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.

template

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.

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 3MB (1,536 pages) to a user database configured for 2K logical pages 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 for a server configured for 2K logical pages:

alter database production 
on userdata1 = "10M"
log on logdev = '2.5m'

Example 4

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

Example 5

Alters the template for the pubs3 database:

alter database pubs3
set template = new_pubs_template_db

Example 6

Changes the durability level of a disk-resident database with relaxed durability:

alter database pubs7 set durability=at_shutdown

Example 7

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

Example 8

Changes pubs2 database to page-level compression:

alter database pubs2 
set compression = page

Example 9

Changes the pubs2 database to use LOB compression:

alter database pubs2 set lob_compression = 100

Example 10

This example modifies the pubs database to change the length of its in-row LOB columns to 400 bytes:

alter database pubs
  set inrow_lob_length = 400

Example 11

Removes 50MB of database sales_db from device mylogdev:

alter database sales_db log off mylogdev='50M'

This example removes the highest-numbered logical pages of sales_db that are on mylogdev, up to a maximum of 50MB.

Example 12

Removes space for database sales_db from device mylogdev, specifying exactly which pages are to be removed:

alter database sales_db log off mylogdev from 7168 to 
    15360

Because logical page 15360 is an allocation page, this example affects all logical pages on mydev from 7168 through 15359. It does not, however, affect page 15360, nor does it affect any pages in the named range that are not physically located on mylogdev.

Usage


Restrictions


Using alter database for archive databases

You can use alter database to add space to the modified pages section of an 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]]...] 

Altering in-memory and relaxed durability databases


Backing up master after allocating more space


Placing the log on a separate device


Altering databases for compression


In-row LOB columns


Shrinking log space

Regarding the log off variant of alter database:


Getting help on space usage


The system and default segments


Using alter database to awaken sleeping processes


Using for proxy_update

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

The permission checks for alter database differ based on your granular permissions settings.

Granular permissions enabled

With granular permissions enabled, for sybsecurity, you must be the database owner or have any of these privileges:

own database (on sybsecurity) or manage auditing.

For all other databases, you must be database owner or have the own database privilege (on the database).

Granular permissions disabled

With granular permissions disabled, you must be the database owner, a user with sa_role, or a user with sso_role (for sybsecurity).

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 optionsalter size

  • Previous value – NULL

  • Current value – NULL

  • Other information – NULL

  • Proxy information – original login name, if a set proxy is in effect

See also

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