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.

  • To actually move the data & rework it you will probably need some more space in your tlog file & your database file. I would try to get a disk extension for log and an extra disk for your your database file. to temporarily store the data for your archive Feb 10 '17 at 14:01
  • I'm assuming that exporting the data (bcp) to another drive, dropping and recreating the table with the better design is not an option? - Does your source table have a primary key or unique index? Feb 10 '17 at 14:29

If allocating space or another drive is impossible, then there might be ways you can do this with minimal growth, assuming you can put the database into RESTRICTED_USER mode or otherwise prevent users/applications from trying to modify the data while you fix it.

First off, don't shrink your data or log files at this point. You'll free up space on the disk temporarily, but the files will just grow again - so as with most shrink operations this is pointless.

Next, take one of the char(10) columns. Ensure that you know the largest string contained:

SELECT MAX(DATALENGTH(CONVERT(varchar(10), char10_column))) 
FROM dbo.table;

Add a new, nullable, varchar(that length) column. (I haven't supported 2005 in some time, so I forget if this is fully or partially online - in theory, this shouldn't be a size-of-data operation, but I don't know if 2005 was smart enough to not treat it as one.)

ALTER TABLE dbo.table ADD newvarchar10_column varchar(10);

In small chunks, as I describe here for deletes, update that new column to have the value of the old column, and set the old column to NULL. This will add to the table size gradually, but will have minimal impact on the log.

UPDATE TOP (?top?) dbo.table -- not sure what your ?top? sweet spot will be
  SET newvarchar10_column = oldchar10_column,
      oldchar10_column = NULL
  WHERE oldchar10_column IS NOT NULL;


Once the data is all located in the new column, dropping the old column should be minimal work for the database, since there isn't any actual data to log. If this column is involved in any indexes or constraints, or schema-bound views/functions, you'll have to deal with those first.

ALTER TABLE dbo.table DROP COLUMN oldchar10_column;

Then rename the new column so that it look like the old column.

EXEC sys.sp_rename N'dbo.table.newvarchar10_column', 
       N'oldchar10_column', N'COLUMN';

Don't forget to update any procedures or code that still think this column is a char(10).

This is just a theory. You might want to test it on a non-critical system first to test the effects on data/log files, figure out the sweet spot for ?top?, etc.

  • Thankyou for this. Wouldn't this require an index rebuild to reclaim the space? or would this scenario be one that frees up internal space for the db to then have room to regrow without taking up more space on the disk. Edit: Just so you're aware, I won't be able to try this (or other possible solutions) until possibly next week.
    – MrVimes
    Feb 11 '17 at 13:09
  • @MrVimes The goal of my approach is not to free up space. It's to prevent an immediate space explosion. Feb 11 '17 at 16:02

You shouldn't have issues altering the columns to a lower size. You can only alter column types one at a time with ALTER TABLE statements anyway. While the total data file size will grow intermittently, it can't grow bigger than the original with corrected column types.

So here are the steps I would take:

1) Ensure that there is a primary key, or at the very least a clustered index on the table.

2) Run the ALTER TABLE ALTER COLUMN statements one at a time

3) If the database runs out of space, run a DBCC SHRINKFILE on the appropriate file.

4) Continue with steps 2 and 3 until all columns are their new appropriate types

5) Rebuild all indexes on the database to remove the fragmentation caused by the previous steps.

Very easy. I've also gone the route of creating a new table with correct definitions, copying the entirety of the existing table to the new one, dropping the original table and renaming the new one to the original. However to do this you'd need more space at least intermittently. You could try putting one of the SQL Server data files on a network drive in the interim, but I think just altering the existing columns as outlined above will be easier.

  • 1
    I have tried in the past. The DB does grow considerably during an alter table alter column operation, so much so that it runs out of space and the operation fails.
    – MrVimes
    Feb 14 '17 at 16:14
  • So you are saying there is not enough space to update a single column in that single table? If that is the case you won't have many good options. You might need to change up your physical hardware -- you don't want to get into a situation where you are doing a RBAR update. Feb 15 '17 at 2:23

Aaron Bertrand gave a great answer, to answer your original question "no" sql server does not deallocate those pages when a delete happens, truncate yes, but delete no. The source table will have the same amount of reserved space.

  • If the table has a clustered index then pages are deallocated when they are empty - except for the last page in the table, For a heap you need the TABLOCK hint. Feb 11 '17 at 9:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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