How to free-up space allocated to text/image fields in Sybase ASE

This how-to explains how to reclaim space from a Sybase ASE text field.

First of all, if you want your text/image fields to use the least possible space, you must define them to allow nulls on table creation, or when altering the table.

You can easily monitor the space used by text and image fields using sp_spaceused:
sp_spaceused objname, 1

The output will usually have a "t" followed by the objname for text fields, for example if you a table named "test_table" with one text field,

create table test_table (
id int not null,
description text null,
picture image null)

create unique index id_ind on test_table(id)

This will implicitly create and text/image index named ttest_table which will actually hold all the text pointers of the specific table.

index_name     size     reserved     unused    
 -------------  -------  -----------  --------- 
 id_ind         2 KB     16 KB        14 KB     
 ttest_table    2 KB     16 KB        14 KB 

In order to deallocate the space reserved by text fields in a Sybase ASE table, you can use the following command:

sp_chgattribute objname, "dealloc_first_txtpg", 1 

where objname is name of table or index.

The command above sets the corresponding text pointer to null after deallocating the previously referenced text or image pages. This result in reduced space allocation for null text/images columns.

Note that sp_chgattribute changes the dealloc_first_txtpg value for future space allocations or data modifications of the table or index. It does not affect the space allocations of existing data pages.

Also note that text and image pages are allocated space even when you perform a NULL update. You can use dealloc_first_txtpg to remove these empty text pages from the table. A new update to the column results in reallocation of a text or image page.

Therefore this is what you have to do in order to free up space when you already NULL values in your text fields, and they were created dealloc_first_txtpg=0. We are using below the test_table created earlier:

sp_chgattribute test_table, "dealloc_first_txtpg", 1
update test_table set description = NULL where datalength(description) = 0
update test_table set picture = NULL where datalength(picture) = 0

Warning:  Steps 2,3 could easily fill up the transaction log! If you have too many rows and/or small transaction log you have to do the updates in small batches. One method is to use unique keys, eg:

update test_table set description = NULL where description = NULL and id >1000

Finally, you can notice the space reserved by text/image fields there before and after the update using sp_spaceused as described earlier.