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.


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]




ANSI SQL – Compliance level: Transact-SQL extension.


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


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


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


Values in event and extrainfo columns of sysaudits are:



Audit option


Command or access audited

alter database

Information in extrainfo
  • 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

Related reference
create database
disk init
drop database
load database