Sybase Space issues

Posted on 2005-03-20
Medium Priority
Last Modified: 2008-01-09

I've got a question regarding data segments on databases which end up full eg 99% full and we need the data ie can't really delete old tables etc what can we do?

They end up full because the data is growing and a minimum amount of historical data is needed.  Thecdata is BCP'd in daily and there is not really much saying what the volumes will be and hence the fact that the data segment gets fully on a regular basis.

What can we do?

1. Ask for more space how can we work how much more space to ask for?

2. Look out for fragmentation? what can we do?

3. Drop and recreate indexes that appear to gooble up more space then the table data itself?

5. Anything else?

Question by:azsat
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 24

Accepted Solution

Joe Woodhouse earned 500 total points
ID: 13587613
I first want to make the point (which maybe I don't need to, apologies if you know all this already) that in Sybase, segments are not containers. No data is stored on a segment. Data is only stored on fragments (one piece of one database on one device), and segments label or point to what type of data is allowed to be stored on those various fragments.

So "data segments filling up" really means "database fragments which are labelled as only being for 'data' are filling up".


1) There is a stored procedure built-in to every version of Sybase ASE called "sp_estspace". You run it against a table and supply a hypothetical rowcount, and it will tell you how much space you'll probably need. You can override its defaults in terms of how full varchars will be, etc.

2a.i) If you're running ASE 11.9.2 or above, there is a tool called "optdiag". You can run this per table - it takes a syntax very much like "bcp" and like bcp is run from the command line. Look for "Data Page Cluster Ratio" - this is a number between 0 and 1. Higher is better! Treat this as a percentage measure of "how well packed/unfragmented is my table?" If it falls below 0.9, that indicates 10% fragmentation, which in my opinion indicates time to do something about it.

2a.ii) if you're running ASE or above, you can use the new built-in function "derived_stat", which can indicate the data page cluster ratio within an SQL expression. This might be easier to work with and is more useful for providing a summary report of fragmentation in your tables.

2b.i) In all versions of ASE, dropping and recreating the clustered index fully defragments the table. This also requires an outage because it uses an exclusive table lock, and is requires free space in the database equal to 120% of the table. If there isn't a clustered index, consider making one of your indexes clustered instead. You could also create a dummy clustered index and then drop it again.

2b.ii) In all version of ASE, bcp'ing out all rows in the table, truncating the table, and then bcp'ing them in again will also fully defragment the table. This also obviously requires an outage. It shifts the burden of free space from the database to filesystem.

2b.iii) In ASE 11.9.2 and above, if you have changed the locking scheme on a table to either "datapages" or "datarows" (collectively these are "data-only locking", or "DOL"), you can run "reorg rebuild" on the table. This also requires an exclusive table lock, and needs the "select into" option enabled on the database (which has implications for any transaction dumps taken), but it does not require the free space, and will be faster than drop & recreate clustered index.

3) Text or image columns are reported as index space by tools such as "sp_spaceused". If you run the more verbose version of this proc, you can get a report per index:

sp_spaceused "table", 1

Also, if you're running ASE or above, you can setup the "MDA" monitoring tables, which can tell you when an index was last used. If you monitor for a while (a week or two?) and an index is never used, perhaps you can simply drop it.

4) You didn't have a number 4. 8->

5) Look out for "heap tables" - tables without a clustered index. There are particularly prone to fragmentation. Also (if on ASE 11.9.2 or above) use DOL locking schemes sparingly, because DOL gets a lot more fragmented than the default "allpages" locking. A DOL heap table is a recipe for fragmentation!!

Good luck!

Author Comment

ID: 13594178
LVL 24

Expert Comment

by:Joe Woodhouse
ID: 13596984
You're welcome, hope it wasn't too much detail! 8->

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying 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

We are witnesses that everyone is saying that our children shouldn't "play" with a technology because it is dangerous. This article is going to prove that they are wrong.
If you're a modern-day technology professional, you may be wondering if certifications are really necessary. They are. Here's why.
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
Suggested Courses

764 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