• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 560
  • Last Modified:

Modify column type using least space

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.
  • 2
  • 2
2 Solutions
Raja Jegan RSQL Server DBA & ArchitectCommented:
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.
SeskyAuthor Commented:
Please note that we do not have enough space to create the flat file as there is only 5GB left on the server :(
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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..
Joe WoodhousePrincipal ConsultantCommented:
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! :)
SeskyAuthor Commented:
Agreed. Thank you for all your comments.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now