Modify column type using least space

Posted on 2009-12-24
Last Modified: 2012-05-08
Have a sybase table that occupies 40GB of size. 30GB of this space is occupied by one column which is defined as type "Text". As the table has 15million rows and each text field value is reserved 2KB of space, this seems right. The issue now is that there is only 5GB of space avaialble and this text field is not required.

Is there a way to drop / modify this filed type alone and save the 30GB of space given that there is only 5GB available? The table also have a couple of other indexes which about about 0.5 GB each. It has been suggested that we need another 40GB of space for any alter / modify operation on the original table that occupies 40GB. Is this true.
Question by:Sesky
    LVL 57

    Accepted Solution

    This should be faster and efficient:

    1. Drop all your Constraints and Indexes
    2. Create a view on that table excluding this text column
    3. BCP out the view to a flat file
    4. Drop both the view and table.
    5. Create the table again without that text column.
    6. BCP in the data to the table.
    7. Create the constraints and indexes which were dropped earlier.

    Author Comment

    Please note that we do not have enough space to create the flat file as there is only 5GB left on the server :(
    LVL 57

    Expert Comment

    by:Raja Jegan R
    >> It has been suggested that we need another 40GB of space for any alter / modify operation on the original table that occupies 40GB. Is this true.

    Yes, No other go in this scenario..
    To do what I mentioned you need atleast 10GB more than what you have currently..
    LVL 24

    Assisted Solution

    by:Joe Woodhouse
    Any kind of rebuild needs space enough to hold a copy of the table. It doesn't really matter if that space is inside the database or an outside filesystem, you can use a different method to make use of either.

    Given you only want to do this once, it's better to have the space at the filesystem level because once you add it to the database it is very very difficult to shrink it down again (and there is no supported way of doing so).

    Sadly while there is an "alter table ... drop column" available in most modern versions of ASE (you didn't say which Sybase database product or version you're using - Sybase has multiple database products), but that still requires a data copy and hence free space in the database. :(

    You don't however need a full 40Gb for this. The method you've already been given above will succeed with maybe only about 10Gb more of space, possibly as little as 5Gb more. You really only need free space in a file system to hold the data you actually care about...

    What operating system are you using? It is possible to do clever things like this on any UNIX or Linux O/S:

    1. create a named pipe
    2. cat from the named pipe to gzip, telling gzip to read from stdin (ie don't attempt to rewrite the source file)
    3. bcp from view (as above) to named pipe

    I've had a lot of success with this, it's not unusual to see bcp files be 25% or smaller, and if you use least compression (gzip -1) it really doesn't add much time provided you have more than two CPUs overall.

    But really, the simplest answer here is to provision more disk. Disk is cheap these days, there's no excuse for ever running out. You can buy laptops with 1.5Tb of disk! :)

    Author Comment

    Agreed. Thank you for all your comments.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How to improve team productivity

    Quip adds documents, spreadsheets, and tasklists to your Slack experience
    - Elevate ideas to Quip docs
    - Share Quip docs in Slack
    - Get notified of changes to your docs
    - Available on iOS/Android/Desktop/Web
    - Online/Offline

    Create and license users in Office 365 in bulk based on a CSV file. A step-by-step guide with PowerShell script examples.
    The recent Microsoft changes on update philosophy for Windows pre-10 and their impact on existing WSUS implementations.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    759 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

    Need Help in Real-Time?

    Connect with top rated Experts

    10 Experts available now in Live!

    Get 1:1 Help Now