Database: expanding and shrinking database

Even if administrator decided to create the database assigning it a lot of spare disk space, it comes to a moment, when a database needs to be expanded. Assuming database has currently size of 55 MB, additional 5 MB of disk space can be assigned to it using following command:

USE master 
GO 

ALTER DATABASE ALFA 
ON AlfaDataDev='5M'
GO

The result, could be like:

Execution time: 0 seconds
Extending database by 1280 pages (5.0 megabytes) on disk AlfaDataDev
Warning: The database 'ALFA' is using an unsafe virtual device 'AlfaDataDev'. The recovery of this database can not be guaranteed.
Execution time: 0.031 seconds

Conditions that need to be met to succeed in database expansion are:

• the current database needs to be master
• a device with free space on it must be available
• size mentioned in ALTER DATABASE command determines how much space will be added to the database (not a total size after expansion)

So after executing of that statement, current size of ALFA database is 60 MB.

Of course from time to time, administrator discovers databases consuming too much disk space. If this is not a temporal condition, he can also release the space, so it could be taken by other databases. We call such action "shinking database". This operation is slightly more complicated, as database needs to be in single user mode. Let's suppose dba  wants to decrease the size by 3 MB:

• The current database must be master at the begining
• Database must be switched to single user mode, what is done in Sybase using sp_dboption procedure
• The main command to shrink database is executed
• Database can be brought back in multi user mode

USE master
GO

EXEC sp_dboption ALFA, 'single user', 'true'
GO

ALTER DATABASE ALFA 
OFF AlfaDataDev = '3M'
GO

EXEC sp_dboption ALFA, 'single user', 'false'
GO

Now the size of the database is 57 MB.

The same way also the log could be expanded. If during shrinking an error would appear, suggested is  to perform a dump tran and... try again. 

The same way, log can be expanded (instead of ON clause use LOG ON). ALTER DATABASE allows also other database settings to be changed. See full description under: http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36272.1570/html/commands/X61977.htm

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}]
 [, 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]