In a largely experimental attempt to reduce table space usage I altered a char(10) column to char(3) (The column's contents are never more than three characters long)

Steps (Not carried out in a single batch)

use thedatabase;
alter table thetable
alter column thecolumn char(3) null

DBCC CLEANTABLE (thedatabase,'thetable', 500000); 
/* Apparently not needed for fixed length column but just in case*/


The operation completed successfully, but the table actually grew in size. I know this can happen due to the way the sql engine modifies a column (creates a copy alongside and then copies the column contents) so I ran dbcc cleantable on the table, and then dbcc shrinkfile on the database file. None of these operations made any difference to the table size.

The table has no index, so not possible to run a rebuild index operation.

So my question is, why did the table grow, even after commands to shrink it.


1 Answer 1


This has most likely to do with forwarded records, the new char(3) value is copied to a new allocation unit (page) leaving a forwarding pointer in its place. You can check if they exist like this.

    OBJECT_NAME(ps.object_id) as TableName,
    i.name as IndexName,
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, 'DETAILED') AS ps
INNER JOIN sys.indexes AS i
    AND ps.index_id = i.index_id
-- WHERE forwarded_record_count > 0
   WHERE ps.object_id = object_id(<TABLENAME>)

The only way to fix this since you are on SQL Server 2005 would be to create a clustered index on the table and then to drop it. SQL 2008 would allows you to do ALTER TABLE <TABLE> REBUILD.

DBCC CLEANTABLE only cleans up variable length columns typed as varchar and nvarchar, see: DBCC CleanTable by Steve Stedman.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.