?
Solved

Sybase device sizes procedure

Posted on 2011-10-21
18
Medium Priority
?
2,081 Views
Last Modified: 2012-06-27
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

0
Comment
Question by:bejhan
  • 9
  • 5
  • 4
18 Comments
 
LVL 1

Author Comment

by:bejhan
ID: 37008012
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
 
LVL 1

Author Comment

by:bejhan
ID: 37008197
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
 
LVL 1

Author Comment

by:bejhan
ID: 37008223
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 13

Expert Comment

by:alpmoon
ID: 37009612
What is Sybase ASE version?
0
 
LVL 1

Author Comment

by:bejhan
ID: 37009707
Oh sorry, should have mentioned that in the question. Version 15.
0
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37010335
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
 
LVL 13

Expert Comment

by:alpmoon
ID: 37010343
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
 
LVL 1

Author Comment

by:bejhan
ID: 37020073
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37029325
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
 
LVL 13

Expert Comment

by:alpmoon
ID: 37032290
Have you tried the proc replacing obsolete where clause with 'dev.vdevno = usg.vdevno' ?
0
 
LVL 1

Author Comment

by:bejhan
ID: 37032755
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
 
LVL 13

Expert Comment

by:alpmoon
ID: 37035855
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37037284
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
 
LVL 1

Author Comment

by:bejhan
ID: 37041090
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
 
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 37042122
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
 
LVL 1

Author Comment

by:bejhan
ID: 37042158
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
 
LVL 24

Accepted Solution

by:
Joe Woodhouse earned 2000 total points
ID: 37042184
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
 
LVL 1

Author Closing Comment

by:bejhan
ID: 37047200
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Applications for our next round of the Experts Exchange Scholarship Contest are starting to roll in. It made us wonder what our past winners are up to these days. Here's a look at what four winners experienced with the contest and what they're doing…
In the below post we have mentioned the best hosting type for startups. Also, check out some of the superlative web hosting companies that are proposing affordable web hosting solutions to host your startup website.
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question