sp_spaceused display huge index size eveb though index was dropped

[Former Member]

Sybase version: Adaptive Server Enterprise/12.5.4/EBF 15446 ESD#8/P/Solaris AMD64/OS 5.10/ase1254/2105/64-bit/OPT/Sat Mar 22 04:05:46 2008

Good afternoon,

I have a question about sp_spaceused information.

For a table, sp_spaceused returns

                                      rowcount    reserved           data               index               unused

REPOSITORY_SPACE    2875956    11855104 KB    140596 KB    11710208 KB    4300 KB

The table has an index on a NUMERIC field.

This table has a field of type IMAGE

I don't understand why the space used by my index is so huge compared to the data size.

I even drop the index, run reorg rebuild, delete statistics, sp_flushstats but the values don't change.

Could anyone explain me the figures and why my index size is still present.

Thank you

Simon

[Former Member]

Thanks all for your valuable information.

After reading carefully Sybase info center, it becomes clear as water.

Thanks all and have a nice week-end.

[Former Member]

Just to add what has been already said here, if you look in sysindexes table in the same database you should be able to find a row for your table with indid 255 which represents the image data storage structure.

Look at indid column description at the link below.

http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.dc36271_36272_36273_36274_1250/html/re...

warm regards,

sudhir

[Former Member]

Hi Simon

It could be because some of your data has the type "text" and in ASE12.5 sp_spaceused provides information about the space used for text data as index_size.

Vincent

[Former Member]

This message was moderated.

[former_member182090]

You have in fact already given the answer yourself

Data in text/image columns is, from a space accounting point of view, seen as an 'index', even though it actually is not an index. This could well explain why you're seeing those numbers.

To get confirmation, run "sp_spaceused REPOSITORY_SPACE, 1", which will list the space used per 'index'. Text/image data is displayed as an index named "t" followed by the table name, so in your case I would expect it to be named "tREPOSITORY_SPACE".