asked on

Sybase space related queries.

Hi,

My environment is as below:
OS: SUSE Linux 11 SP1
Sybase ASE 15.5 with partitioning license.

Need some help to below queries:

1) How to calculate the space used by database?
2) How to calculate the space used by each table in database?
3) How to calculate the space used by each table on a particular segment?
4) How to calculate the free space left in database?
5) How to calculate the space used by partition tables on each partition?

Thanks and regards,
Ranjit

ASKER CERTIFIED SOLUTION

Joe Woodhouse

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

vjirinec

In the install of Sybase, there should also be a CD/DVD which has the client side drivers as well as the client side tools. One of the tools included is Sybase Central. This tool is very helpful in quickly answering all of your questions. In addition, via the tool, you can see each query that it performs. Once you configure the tool for your database, you will see the segments included in each database and the amount of space used/free in each segment.

vjirinec

Sorry, hit return too quickly. Based solely on my experience, I have found the sp_spaceused command to be unreliable.

Joe Woodhouse

Oh, that's interesting, because my experience is the opposite. Sybase Central seems to use sp_helpsegment for its space calculations, which is notoriously unreliable. I've seen space usages of 110% and -20% reported sometimes. :)

sp_spaceused is slower to run, but that's because it's working from GAM and OAM pages. It's accurate to +/- one extent per object, so in a 2Kb page size server, that's +/- 16Kb per object. The only way to get a more accurate count of page utilisation is to run dbcc checkalloc (which is quite a bit slower).

All stats are most accurate immediately after update statistics has been run.

ranjitexpertsexchange

ASKER

Thanks Joe