PRB: Sp_helpdevice Shows Multiple Devices with Device_number 0

  - Microsoft SQL Server version 4.2 for OS/2
  - Microsoft SQL Server version 4.2

SYMPTOMS

In the section for adding database devices ( page 36 for version 4.2), the "Microsoft SQL Server System Administrator's Guide" states that "VDEVNO" must be unique among database devices and dump devices; running sp_helpdevice displays multiple devices with the "device_number" equal to 0 (zero) (which is reserved for the master device).

CAUSE

Unlike DISK INIT, which is used to create database devices, dump devices are created with the stored procedure sp_addumpdevice, which does NOT require a device number as a parameter. The stored procedure sp_helpdevice calculates the "device_number" by dividing the "low" column in sysdevices by 16777216. Because "low" (the starting virtual page number) for dump devices is always 0 (zero), this computation will yield 0 (zero).

For this reason, the master database device and all dump devices have a device_number equal to 0 (zero) when running sp_helpdevice.