Sybase device sizes procedure

I am trying to debug a stored procedure created by someone who is no longer with my firm but am having a bit of difficult because I am not very familiar with the internal workings of Sybase and the procedure has no comments.

When given no argument, the procedure returns:
-Sybase max size (MB)
-Sybase used space (MB)
-Sybase free space (MB)
-For each Sybase device: name, size (MB), used space (MB), free space (MB)

 total                                used                        free
 --------------------------- --------------------------- ---------------------------
 24126.299072                22472.247620                 1654.051453

 devname                                     size                        used                        free
 ------------------------------ --------------------------- --------------------------- ---------------------------
 db_device1                                     9766.099884                 6898.503998                 2867.595886
 db_device2                                     9766.099884                 6898.503998                 2867.595886
 log_device2                                    3906.724884                 6898.503998                -2991.779114
 master                                           30.474884                   28.384186                    2.090698
 sysprocsdev                                     152.474884                  281.337860                 -128.862976
 systemdbdev                                       3.474884                    3.476837                   -0.001953
 tempdb_device                                   400.474884                 1334.675720                 -934.200836
 tempdbdev                                       100.474884                  128.861023                  -28.386139

Open in new window


However, as you can see, some of the devices free space are coming back as negatives because used seems to be greater than size. Obviously this isn't possible, so there must be an error in script.
Could someone help me fix this script?

create proc sp_freedevice @devname char(30)=null as
declare @showdev bit,@alloc float
if @devname=null select @devname="%",@showdev=0
else
select @showdev=1
select @alloc=low from master.dbo.spt_values where type="E" and 
number=1
create table #freedev (name char(30),
size float,
used float)
insert #freedev select dev.name,((dev.high-dev.low)*@alloc+500000)/1048576,
sum((usg.size*@alloc+500000)/1048576) from master.dbo.sysdevices dev, 
master.dbo.sysusages usg where dev.low<=usg.size+usg.vstart-1
and dev.high>=usg.size+usg.vstart-1 and dev.cntrltype=0
group by dev.name
insert #freedev select name,((high-low)*@alloc+500000)/1048576,0
from master.dbo.sysdevices where cntrltype=0 and not exists 
(select * from #freedev where name=master.dbo.sysdevices.name)
if @showdev=1
begin
select devname=dev.name,size=f.size,
used=f.used,
free=(f.size-f.used)
from master.dbo.sysdevices dev, #freedev f where dev.name=f.name
and dev.name like @devname
select dbase=db.name,size=(((usg.size*@alloc)
+500000)/1048576),usage=vl.name from master.dbo.sysdatabases db,
master.dbo.sysusages usg,master.dbo.sysdevices dev,master.dbo.spt_values vl
where db.dbid=usg.dbid and usg.segmap=vl.number and 
dev.low<=usg.size+usg.vstart-1 and dev.high>=usg.size+usg.vstart-1
and dev.status&2=2 and vl.type="S" and dev.name=@devname
end
else begin
select total=sum(size),
used=sum(used),
free=(sum(size)-sum(used)) from #freedev
select devname=dev.name,size=f.size,
used=f.used,
free=(f.size-f.used)
from master.dbo.sysdevices dev,#freedev f where dev.name=f.name
end
go

Open in new window

LVL 1
bejhanAsked:
Who is Participating?
 
Joe WoodhouseConnect With a Mentor Principal ConsultantCommented:
Ok, firstly that isn't actually a device that's holding one or more pieces of a database, it's a shortcut to an area of disk used for backups ("dump device"). It should be excluded by my code as I have a WHERE clause for dev.cntrltype=0.

It doesn't make sense to ask how big this device is as it's just a shortcut (effectively a symbolic link) to a directory on a file system. It's as big as whatever you dump to that directory using that dump device.

Ok, try it with the additional WHERE clauses per below?
select		total = convert(numeric(20,6), 1.0*sum(dev.high - dev.low + 1)/(@@pagesize/4))
,		used = convert(numeric(20,6), 1.0*sum(usg.size)/(@@pagesize/4))
,		free = convert(numeric(20,6), 1.0*(sum(dev.high - dev.low + 1) - 1.0*sum(usg.size))/(@@pagesize/4))
from		master..sysdevices dev
,		master..sysusages usg
where		((dev.status & 2 = 2) or (dev.status2 & 8 = 8))
and		dev.vdevno = usg.vdevno
and		dev.cntrltype = 0
and		dev.vdevno > 0

select		devname = dev.name
,		size = convert(numeric(20,6), 1.0*(dev.high - dev.low + 1)/(@@pagesize/4))
,		used = convert(numeric(20,6), 1.0*sum(usg.size)/(@@pagesize/4))
,		free = convert(numeric(20,6), ((1.0*(dev.high - dev.low + 1)) - (1.0*sum(usg.size)))/(@@pagesize/4))
from		master..sysdevices dev
,		master..sysusages usg
where		((dev.status & 2 = 2) or (dev.status2 & 8 = 8))
and		dev.vdevno = usg.vdevno
and		dev.cntrltype = 0
and		dev.vdevno > 0
group by	dev.vdevno

Open in new window

0
 
bejhanAuthor Commented:
I understand that sizes are being divided by 1048576 because that is number of bytes/MB but what is the purpose of adding 500000?
0
 
bejhanAuthor Commented:
I think maybe this has to do with arithmetic overflow.
@alloc = 2048

1> select name, high, low from  master.dbo.sysdevices
2> go
 name                           high        low
 ------------------------------ ----------- -----------
 master                               15359           0
 tapedump1                            20000           0
 tapedump2                            20000           0
 sysprocsdev                          77823           0
 systemdbdev                           1535           0
 tempdbdev                            51199           0
 db_device1                         4999999           0
 tempdb_device                       204799           0
 disk_archive                             0           0
 db_device2                         4999999           0
 log_device2                        1999999           0

1> select name, ((high-low)*2048+500000)/1048576 from master.dbo.sysdevices
2> go
 name
 ------------------------------ -----------
 master                                  30
 tapedump1                               39
 tapedump2                               39
 sysprocsdev                            152
 systemdbdev                              3
 tempdbdev                              100
Msg 3606, Level 16, State 0:
Server 'SYBASE_mustang', Line 1:
Arithmetic overflow occurred.

Open in new window

0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
bejhanAuthor Commented:
Forcing float arithmetic by multiplying by 1.0 seemed to fix the overflow, still not sure if this is what is causing my negative numbers...

1> select name, ((high-low)*1.0*2048+500000)/1048576 from master.dbo.sysdevices
2> go
 name
 ------------------------------ ---------------------------------------
 master                                                 30.474884033203
 tapedump1                                              39.539337158203
 tapedump2                                              39.539337158203
 sysprocsdev                                           152.474884033203
 systemdbdev                                             3.474884033203
 tempdbdev                                             100.474884033203
 db_device1                                           9766.099884033203
 tempdb_device                                         400.474884033203
 disk_archive                                            0.476837158203
 db_device2                                           9766.099884033203
 log_device2                                          3906.724884033203

Open in new window

0
 
alpmoonCommented:
What is Sybase ASE version?
0
 
bejhanAuthor Commented:
Oh sorry, should have mentioned that in the question. Version 15.
0
 
Joe WoodhousePrincipal ConsultantCommented:
The code is no longer valid for ASE 15. The join between sysusages and sysdevices in all versions prior to 15 was as is written there (ie. WHERE sysusages.vstart between sysdevices.low and sysdevices.high) but that's wrong for ASE 15.

Replace:

dev.low<=usg.size+usg.vstart-1 and dev.high>=usg.size+usg.vstart-1

with:

dev.vdevno = usg.vdevno


Also replace:

dev.low<=usg.size+usg.vstart-1 and dev.high>=usg.size+usg.vstart-1

with:

dev.vdevno = usg.vdevno


Honestly I think the whole thing could be easily rewritten as it performs far more work than is actually necessary to produce that output. :) It also hardcodes assumptions which were not even valid in ASE 12.5 let alone in 15 and above (ie. that the dataserver page size is 2k, which may not be true).

Perhaps most relevantly, now that you're in ASE 15, if you simply run the built-in "sp_helpdevice" it now tells you the free space per device. There's actually no longer any need for this procedure at all, unless it matters that you *only* see those columns and nothing else. Try sp_helpdevice and see if that's enough for your needs. If not, it would be trivial to give you clean, efficient, well-documented code that works properly... unlike what you have now which is none of those things. :)
0
 
alpmoonCommented:
I am not sure what is wrong in the query. But if you are using ASE 15, actually you can modify the queries by using vdevno column in sysusages. In that way you can assure that there is no repetition for a particular device:

insert #freedev select dev.name,((dev.high-dev.low)*@alloc+500000)/1048576,
sum((usg.size*@alloc+500000)/1048576) from master.dbo.sysdevices dev,
master.dbo.sysusages usg where dev.vdevno = usg.vdevno and dev.cntrltype=0
group by dev.name
0
 
bejhanAuthor Commented:
Joe: sp_helpdevice does give the device size and free space in its description column. However, the procedure is used by an application so I would need to parse out these values, which is a pain. Is there anyway to get sp_helpdevice to put these values in their own columns? I didn't see a way to do so in the Sybase documentation. If not, I'm stuck fixing this old procedure (or replacing it).
0
 
Joe WoodhousePrincipal ConsultantCommented:
Have a look at sp_helpdevice's source code (run "sp_helptext sp_helpdevice" in sybsystemprocs) to have a look at how it works.

When I'm back in front of a computer I can probably whip something up for you, too. This isn't complicated SQL and you'll laugh when you see how much unnecessary work your current proc does. :)
0
 
alpmoonCommented:
Have you tried the proc replacing obsolete where clause with 'dev.vdevno = usg.vdevno' ?
0
 
bejhanAuthor Commented:
Joe: I checked out the source for sp_helpdevice. I would appreciate if you could whip something up for me because sp_helpdevice does a lot of things and I'm not experienced enough to know which blocks can be removed and which are required.

Alp: I tried replacing the where clause, I still get negative numbers, but seems slightly more accurate now?

Old procedure result:
 total                       used                        free
 --------------------------- --------------------------- ---------------------------
                24126.299072                22472.247620                 1654.051453

(1 row affected)
 devname                        size                        used                        free
 ------------------------------ --------------------------- --------------------------- ---------------------------
 db_device1                                     9766.099884                 6898.503998                 2867.595886
 db_device2                                     9766.099884                 6898.503998                 2867.595886
 log_device2                                    3906.724884                 6898.503998                -2991.779114
 master                                           30.474884                   28.384186                    2.090698
 sysprocsdev                                     152.474884                  281.337860                 -128.862976
 systemdbdev                                       3.474884                    3.476837                   -0.001953
 tempdb_device                                   400.474884                 1334.675720                 -934.200836
 tempdbdev                                       100.474884                  128.861023                  -28.386139

Open in new window


New procedure result:
 total                       used                        free
 --------------------------- --------------------------- ---------------------------
                24126.299072                 6898.503998                17227.795074

(1 row affected)
 devname                        size                        used                        free
 ------------------------------ --------------------------- --------------------------- ---------------------------
 db_device1                                     9766.099884                  551.907349                 9214.192535
 db_device2                                     9766.099884                 2557.152557                 7208.947327
 log_device2                                    3906.724884                 3508.106232                  398.618652
 master                                           30.474884                   24.907349                    5.567535
 sysprocsdev                                     152.474884                  152.476837                   -0.001953
 systemdbdev                                       3.474884                    3.476837                   -0.001953
 tempdbdev                                       100.474884                  100.476837                   -0.001953
 tempdb_device                                   400.474884                    0.000000                  400.474884

Open in new window


However, based on what Joe said I am worried about the accuracy of this procedure and wish to replace it.
0
 
alpmoonCommented:
I think you just need rounding. Either you can try changing datatypes or add round function.

To change datatypes:

create table #freedev (name char(30),
size numeric(10,0),
used numeric(10,0))
0
 
Joe WoodhousePrincipal ConsultantCommented:
I've run this into my own ASE 15.7 and it gives correct numbers plus seems to match the format of the code you're using now.

You'll laugh when you see how little is needed to give you these numbers. :)

This code will only work in ASE 15.0+, but it will work with any server page size.
select		total = convert(numeric(20,6), 1.0*sum(dev.high - dev.low + 1)/(@@pagesize/4))
,		used = convert(numeric(20,6), 1.0*sum(usg.size)/(@@pagesize/4))
,		free = convert(numeric(20,6), 1.0*(sum(dev.high - dev.low + 1) - 1.0*sum(usg.size))/(@@pagesize/4))
from		master..sysdevices dev
,		master..sysusages usg
where		((dev.status & 2 = 2) or (dev.status2 & 8 = 8))
and		dev.vdevno = usg.vdevno
and		dev.cntrltype = 0

select		devname = dev.name
,		size = convert(numeric(20,6), 1.0*(dev.high - dev.low + 1)/(@@pagesize/4))
,		used = convert(numeric(20,6), 1.0*sum(usg.size)/(@@pagesize/4))
,		free = convert(numeric(20,6), ((1.0*(dev.high - dev.low + 1)) - (1.0*sum(usg.size)))/(@@pagesize/4))
from		master..sysdevices dev
,		master..sysusages usg
where		((dev.status & 2 = 2) or (dev.status2 & 8 = 8))
and		dev.vdevno = usg.vdevno
and		dev.cntrltype = 0
group by	dev.vdevno

Open in new window

0
 
bejhanAuthor Commented:
Wow, much simpler! Just one concern, I am still getting negative values for device "disk_archive".

 total                   used                    free
 ----------------------- ----------------------- -----------------------
           266030.000000             7281.000000           258749.000000

(1 row affected)
 devname                        size                    used                    free
 ------------------------------ ----------------------- ----------------------- -----------------------
 master                                       30.000000               26.000000                4.000000
 tapedump1                                    39.064453               26.000000               13.064453
 tapedump2                                    39.064453               26.000000               13.064453
 disk_archive                                  0.001953               26.000000              -25.998046
 sysprocsdev                                 152.000000              152.000000                0.000000
 systemdbdev                                   3.000000                3.000000                0.000000
 tempdbdev                                   100.000000              100.000000                0.000000
 db_device1                                 9765.625000              800.000000             8965.625000
 db_device2                                 9765.625000             2550.000000             7215.625000
 log_device2                                3906.250000             3650.000000              256.250000

(10 rows affected)

Open in new window

0
 
Joe WoodhousePrincipal ConsultantCommented:
Hmmm! Do me a favour and run and post the output of

   sp_helpdevice disk_archive
   go
   select * from sysdevices where name = "disk_archive"
   go

? I'm trying to see what boundary conditions my code isn't catering for.
0
 
bejhanAuthor Commented:
1> sp_helpdevice disk_archive
2> go
 device_name  physical_name       description                            status cntrltype vdevno vpn_low vpn_high
 ------------ ------------------- -------------------------------------- ------ --------- ------ ------- --------
 disk_archive /export/sybase_dump unknown device type, disk, dump device     16         2      0       0        0

(1 row affected)
(return status = 0)
1> select * from sysdevices where name = "disk_archive"
2> go
 low         high        status cntrltype name                           phyname
         mirrorname                                                                                                                      vdevno      crdate                     resizedate                 status2
 ----------- ----------- ------ --------- ------------------------------ -------------------------------------------------------------------------------------------------------------------------------
         ------------------------------------------------------------------------------------------------------------------------------- ----------- -------------------------- -------------------------- -----------
           0           0     16         2 disk_archive                   /export/sybase_dump
         NULL                                                                                                                                      0                       NULL                       NULL           0

(1 row affected)

Open in new window

0
 
bejhanAuthor Commented:
This works great, thanks!

I have another procedure I am working with that is probably inaccurate as well. Could you take a look at that as well?

http://www.experts-exchange.com/Database/Sybase/Q_27421159.html
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.