Post reply

Table Size is Huge and when Exported to file , size is less

  • July 24, 2006 at 1:55 am

    #62087

    A table "TABLEA" is very large. But the number of

    rows is very small. It contains a column which contains XML data. The column type is Ntext.

    Example:

    sp_MStablespace 'TABLEA'

    Rows DataSpaceUsed IndexSpaceUsed

    1540 572552 328

    Exporting all rows into an .txt file brings a file of only 720 KB.

    I can confirm the table is 500 MB in size, 30 % of the database

    What could be the reason for this ?

    Kind regards,

    Guru

  • July 24, 2006 at 10:46 pm

    #651251

    sp_MStablespace gets it's info from sysindexes, which is the same place sp_spaceused reads from. This data is statistical in nature & is subject to become incorrect. DBCC UPDATEUSAGE corrects sysindexes.

    Have other columns been dropped from the table? DBCC CLEANTABLE reclaims space lost to dropped variable length & text columns.

    Are you using "text in row"? What's the value of:

    select objectproperty(object_id('TABLEA'),'TableTextInRowLimit')

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply