On a SQL Server I inherited, there is a 55GB database on a 70GB drive. There are many small tables, and one big table that is 50GB in size (approx 36GB data, 15GB index). The log file is on a different drive. After
shrinkfile the log file's drive has about 12GB free. Database is in simple recovery mode.
The table is badly designed, for example there are many columns defined as
char(10) that have nothing bigger than 3 characters in them.
There isn't enough free space on the drive to alter these columns and then rebuild the clustered index (when altering a column the database creates a new column then copies the data from the old).
I'm wondering if I can instead do the following:
- Create an archive table with a much better space saving design (appropriately sized columns for the data they contain)
- Incrementally move old data from the huge table to the archive table.
Will the source table reduce in size when data is deleted from it (after being moved to the archive table)? Or would I have to do a clustered index rebuild for that to happen? (In which case I cannot do it because there isn't enough space.)
It now occurs to me that column alteration uses space in the log file, but whenever I do a column alteration on a smaller table, the main data file grows as well.