Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Divide by zero occurred

3 posts in General Discussion Last posting was on 2010-04-22 15:42:08.0Z
Sindre Holdahl Posted on 2010-04-22 08:41:44.0Z
Sender: 4c67.4bd000ac.1804289383@sybase.com
From: Sindre Holdahl
Newsgroups: sybase.public.ase.general
Subject: Divide by zero occurred
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4bd00bc8.4e7b.1681692777@sybase.com>
NNTP-Posting-Host: 10.22.241.41
X-Original-NNTP-Posting-Host: 10.22.241.41
Date: 22 Apr 2010 01:41:44 -0700
X-Trace: forums-1-dub 1271925704 10.22.241.41 (22 Apr 2010 01:41:44 -0700)
X-Original-Trace: 22 Apr 2010 01:41:44 -0700, 10.22.241.41
Lines: 35
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29138
Article PK: 78376

I'm trying to use a script from sybaseteam.com to calculate
sybase database size ,data free,data used,Total log
space,log space free,log space used.

First I got an error: Divide by zero occurred.
After modifying the script it prints out almost every
database but not all.
Anyone know how to print out information about all databases
in master..sysdatabases?

Here is the script:
----------------------------------------------------------------
select 'Database' = convert(char(20),db_name(dbid)),
'DataSize' = str(sum(size * abs(sign(segmap - 4))) / 512.0,
9, 2),
'DataUsed' = str(sum((size - curunreservedpgs(dbid, lstart,
unreservedpgs)) * abs(sign(segmap - 4))) / 512.0, 7, 2),
'DataFree' = str(100.0 * sum((curunreservedpgs(dbid,
lstart,unreservedpgs)) * abs(sign(segmap - 4))) / sum(size *
abs(sign(segmap- 4))), 3) + '%',
'LogSize' = str(sum(size * (1 - abs(sign(segmap - 4)))) /
512.0, 7, 2),
'LogUsed' = str(sum((size - curunreservedpgs(dbid, lstart,
unreservedpgs))* (1 - abs(sign(segmap - 4)))) / 512.0, 7,
2),
'LogFree' = str(100.0 * sum((curunreservedpgs(dbid,
lstart,unreservedpgs))* (1 - abs(sign(segmap - 4)))) /
sum(size * (1 - abs(sign(segmap - 4)))), 3) + '%'
from master..sysusages
where segmap < 5
and dbid <> 2
group by db_name(dbid)
order by DataSize desc
go
----------------------------------------------------------------


"Mark A. Parsons" <iron_horse Posted on 2010-04-22 15:42:08.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Thunderbird 1.5.0.10 (Windows/20070221)
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Divide by zero occurred
References: <4bd00bc8.4e7b.1681692777@sybase.com>
In-Reply-To: <4bd00bc8.4e7b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Antivirus: avast! (VPS 100414-1, 04/14/2010), Outbound message
X-Antivirus-Status: Clean
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bd06e50@forums-1-dub>
Date: 22 Apr 2010 08:42:08 -0700
X-Trace: forums-1-dub 1271950928 10.22.241.152 (22 Apr 2010 08:42:08 -0700)
X-Original-Trace: 22 Apr 2010 08:42:08 -0700, vip152.sybase.com
Lines: 57
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29140
Article PK: 78377

The following is going to filter out the tempdb database as well as any database fragments with segmap bitmaps bigger
than 4 (eg, any fragments with a mix of data and log).

where segmap < 5
and dbid <> 2

-----------------

If your dataserver happens to have a page size greater than 2KB, this query is going to generate incorrect space usage
numbers due to the hard coded 512.

-----------------

I have no idea who/what sybaseteam.com is but fwiw ...

1 - have you tried contacting sybaseteam.com for assistance in debugging their queries?

2 - have you tried looking at the source for the sp_helpdb stored proc? this proc will have most (all?) of the same
type of code ... and it'll be much more accurate (even for dataserver page sizes > 2KB) than this script you're working with

Sindre Holdahl wrote:
> I'm trying to use a script from sybaseteam.com to calculate
> sybase database size ,data free,data used,Total log
> space,log space free,log space used.
>
> First I got an error: Divide by zero occurred.
> After modifying the script it prints out almost every
> database but not all.
> Anyone know how to print out information about all databases
> in master..sysdatabases?
>
> Here is the script:
> ----------------------------------------------------------------
> select 'Database' = convert(char(20),db_name(dbid)),
> 'DataSize' = str(sum(size * abs(sign(segmap - 4))) / 512.0,
> 9, 2),
> 'DataUsed' = str(sum((size - curunreservedpgs(dbid, lstart,
> unreservedpgs)) * abs(sign(segmap - 4))) / 512.0, 7, 2),
> 'DataFree' = str(100.0 * sum((curunreservedpgs(dbid,
> lstart,unreservedpgs)) * abs(sign(segmap - 4))) / sum(size *
> abs(sign(segmap- 4))), 3) + '%',
> 'LogSize' = str(sum(size * (1 - abs(sign(segmap - 4)))) /
> 512.0, 7, 2),
> 'LogUsed' = str(sum((size - curunreservedpgs(dbid, lstart,
> unreservedpgs))* (1 - abs(sign(segmap - 4)))) / 512.0, 7,
> 2),
> 'LogFree' = str(100.0 * sum((curunreservedpgs(dbid,
> lstart,unreservedpgs))* (1 - abs(sign(segmap - 4)))) /
> sum(size * (1 - abs(sign(segmap - 4)))), 3) + '%'
> from master..sysusages
> where segmap < 5
> and dbid <> 2
> group by db_name(dbid)
> order by DataSize desc
> go
> ----------------------------------------------------------------


Bret Halford Posted on 2010-04-22 15:40:51.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.9) Gecko/20100317 Thunderbird/3.0.4
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Divide by zero occurred
References: <4bd00bc8.4e7b.1681692777@sybase.com>
In-Reply-To: <4bd00bc8.4e7b.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4bd06e03$1@forums-1-dub>
Date: 22 Apr 2010 08:40:51 -0700
X-Trace: forums-1-dub 1271950851 10.22.241.152 (22 Apr 2010 08:40:51 -0700)
X-Original-Trace: 22 Apr 2010 08:40:51 -0700, vip152.sybase.com
Lines: 39
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:29139
Article PK: 78378


On 4/22/2010 2:41 AM, Sindre Holdahl wrote:
> I'm trying to use a script from sybaseteam.com to calculate
> sybase database size ,data free,data used,Total log
> space,log space free,log space used.
>
> First I got an error: Divide by zero occurred.
> After modifying the script it prints out almost every
> database but not all.
> Anyone know how to print out information about all databases
> in master..sysdatabases?
>
> Here is the script:
> ----------------------------------------------------------------
> select 'Database' = convert(char(20),db_name(dbid)),
> 'DataSize' = str(sum(size * abs(sign(segmap - 4))) / 512.0,
> 9, 2),
> 'DataUsed' = str(sum((size - curunreservedpgs(dbid, lstart,
> unreservedpgs)) * abs(sign(segmap - 4))) / 512.0, 7, 2),
> 'DataFree' = str(100.0 * sum((curunreservedpgs(dbid,
> lstart,unreservedpgs)) * abs(sign(segmap - 4))) / sum(size *
> abs(sign(segmap- 4))), 3) + '%',
> 'LogSize' = str(sum(size * (1 - abs(sign(segmap - 4)))) /
> 512.0, 7, 2),
> 'LogUsed' = str(sum((size - curunreservedpgs(dbid, lstart,
> unreservedpgs))* (1 - abs(sign(segmap - 4)))) / 512.0, 7,
> 2),
> 'LogFree' = str(100.0 * sum((curunreservedpgs(dbid,
> lstart,unreservedpgs))* (1 - abs(sign(segmap - 4)))) /
> sum(size * (1 - abs(sign(segmap - 4)))), 3) + '%'
> from master..sysusages
> where segmap< 5
> and dbid<> 2
> group by db_name(dbid)
> order by DataSize desc
> go
> ----------------------------------------------------------------

Do the databases that don't show up have segmaps that are > 5?
(mixed data and log is 7).