Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

migrated from stackoverflow.com Jan 21 '12 at 15:00

This question came from our site for professional and enthusiast programmers.

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

2 Answers 2

up vote 3 down vote accepted

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 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.

share|improve this answer
    
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
add comment

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

share|improve this answer
    
Won't help to defrag/reclaim space –  gbn Mar 28 '11 at 6:43
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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