CODEWITHSUNDEEP
sql-server
Chor Wai Chun
Chor Wai Chun

Reputation: 3236

SQL Server move "table free space" to "database free space"

I have a fairly large database (up to a couple hundred shard-ed daily table, total of 1.3TB on the mdf file).

Now as we'd like to try our best to stop the mdf from further growing, we'd remove as much unnecessary data as possible from the existing tables.

What we know:

  1. Deleting data from a table does not reduce mdf file size.
  2. Shrinking file/database is out of question.
  3. Deletion from an old table (say table_20161231) does not free the space to database for new table (say table_20170927) unless the old table is rebuilt.

So we understand a table rebuild is necessary, altering index doesn't seems to help, based on the result of "exec sp_spaceused", the space is still not released.

Hence we are now doing below:

  1. delete from table_20161231 where data = 'unnecessary';
  2. select * into table_20161231_compact from table_20161231
  3. drop table table_20161231

Executing "exec sp_spaceused" again seems to show a positive result by having a lot more "unallocated space" for new tables.

Does anyone know a 1 line method to free out deleted data's space?

Upvotes: 2

Views: 2079

Answers (2)

sepupic
sepupic

Reputation: 8687

So you delete from heaps. Heaps don't release space if you just delete the rows, the possible workarounds are creating clustered index or deleting with tablock:

Deleting Rows by Using DELETE

Deleting Rows from a Heap When rows are deleted from a heap the Database Engine may use row or page locking for the operation. As a result, the pages made empty by the delete operation remain allocated to the heap. When empty pages are not deallocated, the associated space cannot be reused by other objects in the database. To delete rows in a heap and deallocate pages, use one of the following methods.

  • Specify the TABLOCK hint in the DELETE statement. Using the TABLOCK hint causes the delete operation to take a shared lock on the table
    instead of a row or page lock. This allows the pages to be
    deallocated. For more information about the TABLOCK hint, see Table
    Hints (Transact-SQL).
  • Use TRUNCATE TABLE if all rows are to be deleted from the table.
  • Create a clustered index on the heap before deleting the rows. You can drop the clustered index after the rows are deleted. This method
    is more time consuming than the previous methods and uses more
    temporary resources.

Just in case if you have no idea how to use tablock here is an example:

delete dbo.myTbl with(tablock)
where...

P.S.

Hence we are now doing below:

delete from  where data = 'unnecessary';
select * into table_20161231_compact from table_20161231
drop table table_201table_2016123161231

Does anyone know a 1 line method to free out deleted data's space?

Yes, it can be done in 1 command starting with SQL Server 2008:

alter table table_20161231 rebuild

Upvotes: 0

dbajtr
dbajtr

Reputation: 2044

When you delete from a table SQL Server keeps that space to reuse as it assumes that it will eventually get filled anyway. If you want it back the unfortunately shirking is the only way to do it. It can be fraught with danger but as a one off where you know the data will not grow back to that size and you take care of index fragmentation after then it should be OK...

The way you have done it is probably the safest way to do, you can script it and use sp_rename to alter the table name.

Mike Walsh did a decent blog about what happens when you shrink HERE

Upvotes: 0

Related Questions