any way to shrink a table?

Question

any way to shrink a table?

Matt Rose

SSC-Addicted

Points: 425
More actions
December 1, 2008 at 1:57 pm

#126175

I have a Sharepoint 2007 environment attached to a SQL 2005 database... the alldocstreams table is using about 150GB worth of data but is reserving over 220!!!
What is the best way to rectify this? Thanks

Viewing 5 posts - 1 through 4 (of 4 total)

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

shaili Right there with Babe Points: 753 More actions · Answer 1

Only way I know to recover possible space back from allocated space is to reindex with max fill factor that you can afford. If scan density is low, you should be able to recover good amount of space.

Matt Rose SSC-Addicted Points: 425 More actions · Answer 2

i'm sorry to sound dumb...I administrate my companies sql but typically there isn't much needed to be done, can you tell me that more in basic admin terms 🙂

shaili Right there with Babe Points: 753 More actions · Answer 3

If possible, run 'dbcc showcontig' command for your table. Do this at off hours as it can affect performance.

Check for the scan density value.

If the fill factor is high ( 90-100% ) and scan density is low (< 50-60%), there is a pretty good chance that you will be able to 'shrink the table' or reduce the amount of allocated space by reindexing the table.

If the fill factor is low, that explains the additional allocated space for the table. There is nothing much you can do there unless you inrease the fill factor value and reindex the table.

Please read dbcc showcontig and fill factor related article. That will help you understand the situation better.

Matt Rose SSC-Addicted Points: 425 More actions · Answer 4

Table: 'AllDocStreams' (1993058136); index ID: 1, database ID: 7

TABLE level scan performed.

- Pages Scanned................................: 2862

- Extents Scanned..............................: 380

- Extent Switches..............................: 2470

- Avg. Pages per Extent........................: 7.5

- Scan Density [Best Count:Actual Count].......: 14.49% [358:2471]

- Logical Scan Fragmentation ..................: 85.78%

- Extent Scan Fragmentation ...................: 63.16%

- Avg. Bytes Free per Page.....................: 3110.6

- Avg. Page Density (full).....................: 61.57%

this is the table in question..