Table size does not add up

Question

Table size does not add up

BlackKnight

SSC Journeyman

Points: 79
More actions
February 23, 2010 at 6:41 am

#220874

Hi,
I've recently run into a very strange problem with one of our CRM databases.
In particular one of our tables has grown to 60Gb (data size), as can be confirmed by the file size, and results of sp_spaceused.
However there are only 515,000 rows in the table, with only 3 large columns (text, nvarchar(max)).
Doing a select sum(datalength(col_name)) from bigtable on all columns only adds up to 12Gb!
By duplicating the table, migrating the data across, then dropping the original, the space then adds up. But this is a major inconvenience.
Does anyone have any ideas where all this space has gone?!
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

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

sturner SSC-Insane Points: 22411 More actions · Answer 1

Reserved Space is part of the table allocation. This is unused space cause by fragmentation (i.e page splits) and extra growth allocation.

The probability of survival is inversely proportional to the angle of arrival.

BlackKnight SSC Journeyman Points: 79 More actions · Answer 2

Thanks for the reply, how can this space be recovered?

sturner SSC-Insane Points: 22411 More actions · Answer 3

By doing what you did, basically.

The probability of survival is inversely proportional to the angle of arrival.

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 4

You periodically do maintenance to clean up the fragmentation and pack things together (remember your fillfactor here). It's just the way things are that a heavily used table might store extra space in there.

BlackKnight SSC Journeyman Points: 79 More actions · Answer 5

By maintenance do you mean drop and recreate the table as we did?

Or is there another way?

Steve Jones - SSC Editor SSC Guru Points: 736642 More actions · Answer 6

Rebuilding the clustered index will do this as well. Not sure if it's quicker than making a copy of the table and renaming, but you essentially need to move all rows around.