how can i do reorg on table without increaseing database space

learning_sybase used Ask the Experts™
Dear sybase experts,

I have a big table where i am trying to run the reorg rebuild but database space is not much and hence its failing. Table size is approx 30GB and space left in database is also 30GB. Is there a way without increasing database space i can complete reorg rebuild.

Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®


one day passed , no reply ?????
Most Valuable Expert 2012
Distinguished Expert 2018

I've been asked to see if I can help you find a solution since no other Expert has jumped in.

I've never used Sybase so I'm not sure how other database products will related to this question.  I'm an Oracle person.

What are you hoping the reorg to accomplish?

From a quick Google I see there are several types of reorgs in Sybase.  I'm guessing you are wanting to do rebuild?

If so, I don't see how you can to a full rebuild in place without enough space to hold the rebuilt table while the reorg is taking place.

I would look at exporting/dumping the table, dropping it and import the data back in.  In Oracle an export/import can consolidate the data blocks which is what I believe you are trying to do.
Principal Consultant
Most Valuable Expert 2012
Sometimes reorg rebuild is able to "rebuild in place" which does not need extra space. This depends on a lot of factors - what locking scheme is on the table; how many indexes; how much fragmentation exists on the table and the nature of that fragmentation.

And of course your table may not be fragmented at all in which case reorg rebuild will do nothing for you. So first things first: can you post the results of running

exec sp_spaceused [table], 1

Open in new window

and also the header (table-level information, we don't need any column-level information) of running "optdiag" on the table? That's an external tool you run from the OS, like isql and bcp.

We should also have your exact ASE version please as that will also limit what options are available to you.

But it's probably bad news - almost any defragmenter tool (database tables, files, clutter on the living room table) needs some free space in order to move things around in. If you simply have very little free space then you will be very limited in what you can do.

You might try first running the "lesser" versions of reorg, to try to free up some space before running a full reorg rebuild:

reorg compact_space [table]
reorg defrag [table]                        -- if you are using ASE 16.0+
reorg rebuild [table], [index]        -- one index at a time, start with the smallest

Lastly remember it's been possible for a while now in ASE to shrink a database down again, so you could in principle add some database space, reorg, then remove the space you added.

If your ASE license permits, it might also then be time for you to look into ASE compression on this table and possibly also its indexes. Page compression is better than row compression... but it only applies to new rows. To compress existing data you need... reorg rebuild. :)

Serious question: why not add more database space?

I have ever been in a situation when there were good reasons I couldn't permanently add more database space, and this was before it was possible to shrink databases. I used bcp to copy the data out to file, truncated the table, then used bcp to copy it back in again. Of course this still needs disk space at the file level, and it's a complete outage for the duration end to end, but it did what I needed.

PS: Re. "One day passed, no reply" - we're volunteers doing this for free, and may not have set notifications for when new questions are asked.



Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial