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

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

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]]...] 

• 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:

  1. Dump a database.

  2. Perform a dump transaction.

  3. Perform a second dump transaction.

  4. Changed the durability to no_recovery.

  5. Changed the durability to full.

  You cannot perform a third dump transaction. Instead, you must perform a full dump database.

• set compression specifies the database-wide compression level, which applies only to newly created tables.

• You can use set lob_compression by itself or with other set subclauses. However, other set subclauses require the database to be in single-user mode (for example, if you change the durability level of the database).

• Use inrow_lob_length to increase or decrease the in-row LOB length database-wide.

• Changing the inrow_lob_length affects the creation of LOB columns in future create table or alter table add column commands. The valid values are within the range of 0 to the logical page size of the database.

Regarding the log off variant of alter database:

• Although the log off option specifies the range the range of pages to be removed as logical pages, it is the associated physical pages that are actually removed. The logical pages remain in the database as unusable since they form a hole. A hole is one or more allocation units for which there is no associated physical storage.

• Information about which allocation units—space that is divided into units of 256 data pages when you create a database or add space to a database—exist on the devices that are available in the master.dbo.sysusages table, which lists disk pieces by database ID, starting logical page number, size in logical pages, device ID, and starting offset in the device.

• If the specified to page is less than the from page, the pages are switched—that is, the to page becomes the from page, and vice versa. If from and to name the same page, the command affects only the allocation unit containing that page. The command does not adjust the to page in a way that causes a command error.

• The entire device is affected if you do not provide any clauses. This is equivalent to log offdevice from 0. This command specifies physical storage, removing every log page on the specified device from the database:

  alter database sales_db log off mylogdev
  

• If alter database detects an error, it does not execute, and returns a message indicating the reason, such as:

  • The database log becomes too small.

  • The fragments to be removed contain pages that are allocated to syslogs. That is, the active log occupies space in the log fragments to be removed.

  • The amount of log free space after the fragment is removed is too small to accommodate the last chance threshold.

• 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.