sp__deviceinfo

sp__deviceinfo

This procedure displays the information of devices.

Input Parameters:

• min_space – lists all the devices with the specified size of minimum free space on the device in MBs.
• dev_name - name of the device or devices matchs the pattern.

Usage Examples:

• sp__freedevice - displays the information of all the devices.
• sp__freedevice 100,’User_Data’ - displays all the devices like User_Data1, User_Data2  etc., with >= 100MB free space.

create procedure sp__deviceinfo @min_space int = null, @dev_name varchar(64) = null

as

begin

declare @numpgsmb integer /* Number of 'virtual' Pages in MegaBytes */

select @numpgsmb = (1048576 / @@maxpagesize)

select "phyname"=convert(varchar(64), d.phyname),

"name"=convert(varchar(32),d.name),

"d_size"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb),

"d_used"=convert(varchar(6), sum(u.size / @numpgsmb)) ,

"d_free"=((1 + d.high - d.low) / @numpgsmb) - sum(u.size / @numpgsmb),

vdevno=d.low/power(2,24) & 255

into #device_tbl

from master..sysusages u, master..sysdevices d

where u.vstart between d.low and d.high

and d.status & 2 = 2

group by d.name

select

vdevno=low/power(2,24) & 255,

"Physical Name"=convert(varchar(40), d.phyname),

"Device Name"=convert(varchar(28),d.name),

"Size"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb),

"Used"=convert(varchar(6),0),

"Free"=convert(varchar(6),(1 + d.high - d.low) / @numpgsmb)

from master..sysdevices d

where d.name not in (select tmp.name from # device _tbl tmp)

and d.status & 2 = 2

and ( @min_space is null or ((1 + d.high - d.low) / @numpgsmb) >= @min_space )

and ( @dev_name is null or d.phyname like @dev_name or d.name like @dev_name)

union

select vdevno , phyname , name , d_size , d_used , convert(varchar(6), d_free)

from # device_ tbl

where ( @min_space is null or d_free >= @min_space )

and ( @dev_name is null or phyname like @dev_name or name like @dev_name)

order by 1

end

go

More Info of sysdevices:

sysdevices contains one row for each tape dump device, disk dump device, disk for databases, and disk partition for databases. There are four entries in sysdevices in the Adaptive Server distribution media: one for the master device (for databases), one for a disk dump device, and two for tape dump devices.

The columns for sysdevices are:

Name	Datatype	Description
low	int	Not used for dump devices – block offset of virtual page in 2K bytes
high	int	Block offset of last virtual page in 2K bytes
status	smallint	Bitmap indicating type of device, default, and mirror status
cntrltype	smallint	Controller type: Database device=0 Disk dump device or streaming tape=2 Tape dump device=3 – 8
name	sysname	Logical name of dump device or database device
phyname	varchar(127)	Name of physical device
mirrorname	varchar(127) null	Name of mirror device
vdevno	int	Device identification number
crdate	datetime null	Date on which the device was added
resizedate	datetime null	Date on which disk resize was most recently run for this device
status2	int	Additional status bits for this device

Bit representations for the status column.

Decimal	Hex	Status
1	0x01	Default disk
2	0x02	Physical disk
4	0x04	Not used – logical disk
8	0x08	Skip header
16	0x10	Dump device
32	0x20	Serial writes
64	0x40	Device mirrored
128	0x80	Reads mirrored
256	0x100	Secondary mirror side only
512	0x200	Mirror enabled
1024	0x400	Master device is mirrored
2048	0x800	Used internally – mirror disabled
4096	0x1000	Used internally – primary device must be unmirrored
8192	0x2000	Used internally – secondary device must be unmirrored
16384	0x4000	UNIX file device uses dsync setting (writes occur directly to physical media)

sysusages

sysusages contains one row for each disk allocation piece assigned to a database. Each database contains a specified number of database (logical) page numbers.  The create database command checks sysdevices and sysusages to find available disk allocation pieces. One or more contiguous disk allocation pieces are assigned to the database, and the mapping is recorded in sysusages.

The columns for sysusages are:

Name	Datatype	Description
dbid	smallint	Database ID
segmap	int	Bitmap of possible segment assignments
lstart	int	First database (logical) page number
size	int	Number of contiguous database (logical) pages
vstart	int	Starting virtual page number
pad	smallint null	Unused
unreservedpgs	int null	Free space not part of an allocated extent
crdate	datetime null	Creation date
vdevno	int	Device identification number