Managing devices

Dropping a device

After a database has been dropped a device not used by an
 database can usually also be dropped(of course dba may decide not to release disk space). To remove device following command could be used:

exec sp_dropdevice 'BetaDevice'

where BetaDevice is logical name of device that should be removed. After issuing that command only row in sysdevices is removed. File that the device was build on, need to removed manually. The same concerns also a raw device.

If the device is in use - so you have a database using it - don't worry. Such command will end with an error "Device is being used by a database. You can't drop it.".

Resizing a device

It may happen that with time, a device will be too small to accommodate databases build on it. If  there is only enough disk space in file system for a file to be expanded, a device can be extended. Let's assume, we have an AlfaDataDev of size 30MB:

exec sp_helpdevice AlfaDataDev

device_name description....
----------- ----------------------------------------------------------------------------------------------------------
AlfaDataDev  file system device, special, dsync off, directio on, default disk, physical disk, 30.00 MB, Free: 24.00 MB 

To add 40MB to this device issue following command:

disk resize name = 'AlfaDataDev', size = '40M'

Mark, the 40MB is not a new size of the device, but amount that will be added to the device. So finally the device's size will be 70 MB:

exec sp_helpdevice AlfaDataDev

device_name description....
----------- ----------------------------------------------------------------------------------------------------------
AlfaDataDev  file system device, special, dsync off, directio on, default disk, physical disk, 70.00 MB, Free: 24.00 MB 

Disk resize command does not allow to shrink a device.

Setting device options

Devices can have some options set. In the above example option dsync is turned off and directio is turn on. (More about those options can be found in File based device creation). If directio should be turned off for AlfaDataDev following command should be started:

exec sp_deviceattr AlfaDataDev, directio, false

Generally command takes three parameters: logical name of the device, attribute/option name and a new value for the option. Options like dsync or directio require unfortunately server to be restarted, information about it is displayed as a result of the command:

'directio' attribute of device 'AlfaDataDev' turned 'off'. Restart Adaptive Server for the change to take effect.
Execution time: 0.009 seconds