Getting information about devices

The system procedure 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 Adaptive Server. 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, 20 MB

status      cntrltype    device_number     low      high 
------      ----------   --------------    ------   ------- 
3           0            0                 0        9999

Each row in master..sysdevices describes:

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

If you installed auditing, there will also be a separate device for sybsecurity.

The low and high fields represent the page numbers that have been assigned to the device. For dump devices, they represent the media capacity of the device.

The status field in sysdevices is a bitmap that indicates the type of device, whether a disk device will be 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. The status bits and their meanings are listed in Table 16-2:

Table 16-2: 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 Chapter 26, “Developing a Backup and Recovery Plan.”