alter database

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 data manipulation language (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]

To fully encrypt and decrypt databases:

alter database database_name
{[encrypt with key_name | decrypt [with key_name]] [parallel degree_of_parallelism]
| resume [encryption | decryption [parallel degree_of_parallelism]]
| suspend [encryption | decryption]
}

To shrink databases:

alter database database_name        
off database_device {=size  | [from page_number] [to page_number]}      
[, database_device...]      
[with time='time']
[with check_only]

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 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 SAP^® Adaptive Server^® Enterprise.
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). SAP^® 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, the SAP ASE 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. The SAP ASE 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.
  
  The SAP ASE 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 – the SAP ASE server uses ZLib compression. Generally, the higher the compression number, the more the SAP ASE 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 – the SAP ASE server uses FastLZ compression. The compression ratio that uses the least CPU usage; generally used for shorter data.
- 101 – the SAP ASE 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 the SAP ASE 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. The SAP ASE 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 the SAP ASE 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, the SAP ASE server attempts to not log row or page changes to syslogs. However, the SAP ASE 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 the SAP ASE 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.
encrypt with key_name – instructs SAP ASE to fully encrypt the database.
Specifically, the command retrieves the corresponding key ID from the sysencryptkeys system table in the master database and set the encrkeyid column in its related sysdatabases row.

key_name is the database encryption key you used to encrypt the database. If you do specify a different key name, the command fails and SAP ASE displays an error message.
SAP ASE fails to run alter database and displays an error message if the database is already:
- Encrypted with another key.
- Being encrypted.
If you run this command on a partially encrypted database that is not currently being encrypted, SAP ASE treats the command as if you specified the resume encryption option, as long as the key name is the same as the previously specified key.
decrypt [with key_name] – instructs SAP ASE to decrypt the database. When decrypting a database, [with key_name] is optional, as SAP ASE looks up the key ID in the sysdatabases system table. The command fails, however, if you specify key_name with a different key name than what was used to encrypt the database.
resume decryption – instructs SAP ASE to resume the decryption process for the database in which an earlier decryption process was suspended. SAP ASE ignores this command if the database_name is already completely decrypted.
parallel degree_of_parallelism – determines how many worker threads to initiate for the task.
Create a thread for each database storage virtual device, as long as the number is equal to or fewer than "number of worker processes" configuration. The degree_of_parallelism number should be no larger than the number of database devices because additional worker threads do not improve encryption performance. If you do not specify degree_of_parallelism, SAP ASE internally defines the value based on the number of online engines, as well as how the database is distributed across various devices.
resume encryption – resumes the encryption process from the page where encryption was previously suspended.
The command fails if:
- There is an encryption process already running in SAP ASE.
- Encryption was never started on the database.
- The encryption process already completed.
You can use parallel degree_of_parallelism with resume encrypt. If you do not specify parallel degree_of_parallelism, SAP ASE determines the value based on how the database is distributed across various engines.
suspend encryption – terminates all encryption worker threads that are encrypting data. SAP ASE records the progress of encryption so that resume encryption can restart encryption where the previous encryption process stopped. SAP ASE ignores this command if there is no encryption in progress.
off – specifies the device from which you are releasing space for shrinking databases. The off clause includes:
- 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 device.
  database_device includes either the =size parameter or the from or to parameters (not both). The default from page is 0 (zero). If you do not specify any modifiers with the from clause, SAP ASE releases all storage for this database on the indicated device.
- size – specifies the amount of space to be released from the database. alter database accepts a specific size, or a from ... to declaration:
  - Specific size – an integer. If you do not include a unit specifier, SAP ASE uses megabytes as the unit. You can also include “k” or “K” (kilobytes), “m” or “M” (megabytes), “g” or “G” (gigabytes), and “t” or “T” (terabytes). SAP ASE 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. For example:
    - alter database ... off mydev1 = 200 – removes 200 megabytes
    - alter database ... off mydev1 = '2g' – removes 2 gigabytes
  - from ... to declaration – unsigned integers. See below.
  SAP ASE releases the highest-numbered logical pages of this database associated with the indicated device. SAP ASE rounds the value for size up, if needed, to indicate an even number of allocation units.
  
  If you specify more space than is currently in use by this database on the indicated device, SAP ASE limits the specified size or page IDs to the amount of space available.
- from page_number – specifies the low end of the range of logical pages to be released. SAP ASE rounds the from value down, if needed, to represent the first page of the allocation unit containing the indicated page. The default value for from is 0 (zero).
- to page_number – specifies the high end of the range of logical pages to be released. SAP ASE rounds the to value up, if necessary, to represent the last page of the allocation unit containing the indicated page. The default value for to is the highest-numbered logical page in the database.
  However, if the to page is an allocation page itself (which is the starting page of an allocation unit) and is not the same as the from page, the alter database ... off command does not affect the allocation unit containing the specified to page. SAP ASE assumes that a user requesting a precise page range does not intend to affect the specified end page, so it decrements the end page rather than increasing it.
  
  Note: SAP ASE does not issue an error message if you indicate a from page value that is greater than the to page value. Instead, SAP ASE swaps the from and to values before applying the rounding logic.
- with check_only – checks for:
  - The expected results of shrinking the database rather than actually shrinking the database.
  - Tables with text columns that are not marked as having text back-linked to their owning rows. All text and image columns are required to have back-linked pointers to the home data row in the first text page.
  - Sufficient room in the remainder of the database to accommodate everything that must be moved. Any indication of a potential problem is reported.
  - And reports any potential problems where a significant amount of time could be spent sorting the index
  When checking the indexes, if the results indicate that there could be enough duplicate key entries that the command will spend a significant amount of time sorting the index, the index is reported as a problem. The recommendation is that the index should be dropped before shrinking the database and the index be re-created after the database is shrunk.
  
  Results of the checks being done by with check_only can be compromised by any other work being performed on in the database while the checks are running, or by work that was done after the checks are run but before the actual shrink is completed.
- with time='time' – specifies the maximum length of time alter database ... off may run, and is specified as hours, minutes, and seconds (HH:MM:SS). If you do not include the full specification, alter database interprets the missing sections for time as:
  - XX:YY is interpreted as MM:SS.
  - XX is interpreted as minutes.
  - Empty sections are treated as zero. For example, alter database interprets both “::30” and “:30” as 30 seconds. However, alter database interprets “1::” as 1 hour, and "2:" as 2 minutes.
    If alter database ... off does not finish within the specified period of time, work in progress is abandoned, and the command exits with an error message. Any completed work remains done.
Note: Use either alter database off or alter database log off to shrink the log segment. Both commands perform the same function.

Server’s Logical Page Size	Database Extended By
2K	1MB
4K	1MB
8K	2MB
16K	4MB

Examples

Adds 3MB to Database – Adds 3MB (1,536 pages) to a user database configured for 2K logical pages on a default database device:
```
alter database mydb
```
Adds 3MB to Database on Device – Adds 3MB to the space allocated for the pubs2 database on the database device named newdata:
```
alter database pubs2 on newdata = 3
```
Adds 10MB for Data and 2MB for Log – 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 Durability Level – 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
```
Changes Template – Alters the template for the pubs3 database:
```
alter database pubs3
set template = new_pubs_template_db
```
Changes Durability Level of Disk-Resident Database – Changes the durability level of a disk-resident database with relaxed durability:
```
alter database pubs7 set durability=at_shutdown
```
Changes DML Logging Level – 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
```
Changes Compression Level – Changes pubs2 database to page-level compression:
```
alter database pubs2 
set compression = page
```
Changes Database to LOB Compression – Changes the pubs2 database to use LOB compression:
```
alter database pubs2 set lob_compression = 100
```
Modifies Length of In-Row LOB Columns in Database – 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
```
Removes 50MB of Database from Device – 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.
Specifies Pages to Remove – 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.
Encrypts an Existing Database – This example alters an existing database called existdb for encryption using an encryption key called dbkey:
```
alter database existdb encrypt with dbkey
```
The example does not specify the parallel degree, leaving it up to SAP ASE to determine how many worker threads should be initiated to encrypt existdb in parallel.
Suspends an Encryption of a Database – This example suspends an encryption operation on a database called existdb:
```
alter database existdb suspend encryption
```
Resumes Encrypting a Database – This example resumes a suspended encryption on a database called existdb:
```
alter database existdb resume encryption
```

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

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

Setting	Description
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).
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:

Information	Values
Event	2
Audit option	alter
Command or access audited	alter database
Information in `extrainfo`	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