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
  • 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

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Microsoft provides a rich set of technologies for High Availability and Disaster Recovery solutions.
Why WooCommerce is one of the majorly favored choices when it comes to having an eCommerce store. This article will acquaint you with some reasons that I believe make it one of the best eCommerce platforms available.
SQL Database Recovery Software repairs the MDF & NDF Files, corrupted due to hardware related issues or software related errors. Provides preview of recovered database objects and allows saving in either MSSQL, CSV, HTML or XLS format. Ensures recov…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
Suggested Courses

621 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