2

We have a huge database (300GB) where most of the space is occupied by a large table. This table has a number of columns that were defined as float, ocuppying 8 bytes each. However recently we realized that only 4 bytes are needed and we have to change the size of each column so we have to run queries like this one for every column (around 6 columns):

alter table [HugeTable] alter column [Col] float(1)

My question is: how and when should we reclaim the space after reducing column size? For the question of when we have two possibilities: after each alter table (disadvantage: it takes longer) or after all columns were reduced. For the question of how our thoughts are: db shrink, clustered index rebuild. Also would DBCC CLEANTABLE be useful in our case? From what I could understand it is useful only for variable column types.

Thanks!

EDIT: In the end we decided not to use this approach. Instead we are creating a table with the new structure and gradually moving the data.

Ioana Marcu
  • 551
  • 8
  • 25

1 Answers1

1

I would resize all columns and then run the maintenance on the db. There is no need to do it after each column resize.

Z .
  • 12,657
  • 1
  • 31
  • 56
  • What kind of maintenance would you suggest? – Ioana Marcu Apr 12 '13 at 13:06
  • Shrink database, rebuild indexed, check consistency, etc. All things that you probably have in a weekly (?) maintenance job... – Z . Apr 12 '13 at 13:10
  • Unfortunately because of the db size we can't afford to run those regularly :( a consistency check lasts 13 hours (!!!). so we really need to keep it as quick as possible. Would an index rebuild be enough? I know that shrinking is not exactly recommended – Ioana Marcu Apr 12 '13 at 13:12