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:

[FileSizeMB]    =
[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
sysfiles a;


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 ?

  • Table definition including index definitions might be helpful! – Transact Charlie Mar 28 '11 at 6:46


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 DBCC

dbcc showcontig ('Risk') with allindexes, tableresults;

For example, one of my tables. A billion rows, 70GB table, 450MB index

name      rows       reserved  data     index_size  unused
BigTable  1041302384 71256288  70793880 460832      1576

Edit, Jan 2012

Note that deleted records are not physically removed on DELETE. A "ghost clean up" process does that afterwards.

  • you were right of course. I used the IndexOptimize sp from ola.hallengren.com to automate this work for all index on all tables on all db. – Softlion Apr 23 '11 at 22:17

You can shrink that database files through the Enterprise Manager. Right click on the database, All Tasks->Shrink Database

  • Won't help to defrag/reclaim space – gbn Mar 28 '11 at 6:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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