Getting Information about Devices

sp_helpdevice provides information about the devices in the sysdevices table.

When used without a device name, sp_helpdevice lists all the devices available on SAP ASE. When used with a device name, it lists information about that device. Here, sp_helpdevice is used to report information about the master device:

sp_helpdevice master
device_name  physical_name  description 
-----------  -------------- ------------------------------------------
master        d_master         special, default disk, physical disk, 30 MB
status      cntrltype    vdevno    vpn_low   vpn_high 
------      ----------   -------    -------   --------- 
3           0            0          0         10239

Each row in master..sysdevices describes:

A dump device (tape, disk, or file) to be used for backing up databases, or
A database device to be used for database storage.

The initial contents of sysdevices are operating-system-dependent. sysdevices entries usually include:

One for the master device
One for the sybsystemprocs database, which you can use to store additional databases such as pubs2 and sybsyntax, or for user databases and logs
Two for tape dump devices

If you installed auditing, there is a separate device for sybsecurity.

The vpn_low and vpn_high columns represent the page numbers that have been assigned to the device. For dump devices, these columns represent the media capacity of the device.

The status field indicates the type of device, whether a disk device is used as a default storage device when users issue a create or alter database command without specifying a database device, disk mirroring information, and dsync settings.

Status bits in sysdevices
Bit	Meaning
1	Default disk (may be used by any create or alter database command that does not specify a location)
2	Physical disk
4	Logical disk (not used)
8	Skip header (used with tape dump devices)
16	Dump device
32	Serial writes
64	Device mirrored
128	Reads mirrored
256	Secondary mirror side only
512	Mirror enabled
2048	Used internally; set after disk unmirror, side = retain
4096	Primary device needs to be unmirrored (used internally)
8192	Secondary device needs to be unmirrored (used internally)
16384	UNIX file device uses dsync setting (writes occur directly to physical media)

For more information about dump devices and sp_addumpdevice, See System Administration Guide: Volume 2 Developing a Backup and Recovery Plan.