i have a table with 12 months+ data in it.
I backup the table then delete all rows keeping only rows inserted in the last month.
But the table size won't shrink, nor the underlying physical file.
dbcc showcontig ('mytable') with tableresults;
ObjectName ObjectId IndexName IndexId Level Pages Rows MinimumRecordSize MaximumRecordSize AverageRecordSize ForwardedRecords Extents ExtentSwitches AverageFreeBytes AveragePageDensity ScanDensity BestCount ActualCount LogicalFragmentation ExtentFragmentation
mytable 478624748 PK_Log 1 0 21344 467392 94 8033 237 0 2675 12918 2862,346 64,6361996540647 20,6517532316743 2668 12919 77,8392053973013 71,7757009345794
exec sp_spaceused 'mytable', @updateusage = 'TRUE'
name rows reserved data index_size unused
LogTrace 467392 8984976 KB 8930056 KB 21984 KB 32936 KB
These values means each row of the table is about 19MB!
I tryed to rebuild the index with no change:
ALTER INDEX PK_myindex ON mytable REBUILD
Other things i tryed:
select
[FileSizeMB] =
convert(numeric(10,2),round(a.size/128.,2)),
[UsedSpaceMB] =
convert(numeric(10,2),round(fileproperty( a.name,'SpaceUsed')/128.,2)) ,
[UnusedSpaceMB] =
convert(numeric(10,2),round((a.size-fileproperty( a.name,'SpaceUsed'))/128.,2)) ,
GrowthPct =
case when a.status & 0x00100000 = 1048576 then a.growth else null end,
GrowthMB =
convert(int,round(case when a.status & 0x00100000 = 1048576 then null else a.growth /128.000 end,0)),
[DBFileName] = a.name
from
sysfiles a;
result
FileSizeMB UsedSpaceMB UnusedSpaceMB GrowthPct GrowthMB DBFileName
9742.38 9740.06 2.31 NULL 1 mytable
72.25 15.45 56.80 NULL 1 mytable_log
3.00 0.06 2.94 NULL 1 mytable_indexes
I don't know what to do more, any idea ?
Best Answer
Try
ALTER INDEX ALL ON mytable REBUILD
With the index size value being so high, this points to a non-clustered index. Clustered index size is very small usually.
You can check with the
allindexes
parameter on the DBCCFor example, one of my tables. A billion rows, 70GB table, 450MB index
Edit, Jan 2012
Note that deleted records are not physically removed on DELETE. A "ghost clean up" process does that afterwards.