Best way to reduce table size 2

[icemel]

Ok, we've decided NOT to shrink our database. However, we still have the problem of too much free space in tables.

What is the best way to get rid of the free space? I was thinking just rebuild and repopulate the table.

We only have a few tables with this problem.

Also, what is the best way to avoid free space problems in the future? I know changing data column types to the most efficient type is a good idea. For example, using varchars vs chars and using smalldatetimes. Anything else?

Thanks

 

[SQLBill]

Icemel,

I run DBCC SHOWCONTIG. When it shows my indexes are fragmented, I run DBCC INDEXDEFRAG on the table's indexes. This increases the overall Transaction Log size, but reduces the used space for the database (end result is more 'free' space in the database). Then if the free space is huge (I have my database set to auto grow 5000 MB - so before I reclaim space, I want to make sure I'm not going to immediately grow the database again). If I decide to reclaim the free space and return it to the OS, I run DBCC SHRINKFILE against the Transaction Log file and then the Data file(s).

My rule of thumb is three autogrows. For example, my datafiles are set to autogrow by 5000MB (5G). If the free space is 15000MB (15G), I leave it be. For my database, that's two weeks growth. If it's over 15000MG, then I will shrink it.

-SQLBill

Posting advice: FAQ481-4875

 

[icemel]

SQLBill, so you are not worried about fragmenting your indexes by running DBCC SHRINKFILE?

 

[SQLBill]

I haven't found them to be fragmented by the shrinking. I've run DBCC SHOWCONTIG after a shrinkfile and really don't notice a difference. There is one, but it's so minor that it doesn't matter to me.

-SQLBill

Posting advice: FAQ481-4875