Truncated 200GB table but disk space not released

Question

I have only 2GB left, so I need to remove this history table. This table now is empty but the database disk space not released. And the database file is 320GB.

Answer 1

If you are referencing the actual database file consumption on the volume, then SQL Server doesn't handle that automatically. Just because you removed data from the database doesn't mean the database files will shrink to fit only the existing data.

What you'd be looking for, if you have to reclaim space on the volume, would be shrinking the particular file with DBCC SHRINKFILE. It is worth noting a few best practices, as per that documentation:

Best Practices

Consider the following information when you plan to shrink a file:

• A shrink operation is most effective after an operation that creates lots of unused space, such as a truncate table or a drop table operation.

• Most databases require some free space to be available for regular day-to-day operations. If you shrink a database repeatedly and notice that the database size grows again, this indicates that the space that was shrunk is required for regular operations. In these cases, repeatedly shrinking the database is a wasted operation.

• A shrink operation does not preserve the fragmentation state of indexes in the database, and generally increases fragmentation to a degree. This is another reason not to repeatedly shrink the database.

• Shrink multiple files in the same database sequentially instead of concurrently. Contention on system tables can cause delays due to blocking.

Also of note:

DBCC SHRINKFILE operations can be stopped at any point in the process, and any completed work is retained.

There are surely a few things to consider when doing this, and I'd recommend you take a look at Paul Randal's blog post on what happens when you do this operation.

The first step would definitely be to verify how much space and free space you are actually able to replace, as well as the used space on the file(s):

use AdventureWorks2012;
go

;with db_file_cte as
(
    select
        name,
        type_desc,
        physical_name,
        size_mb = 
            convert(decimal(11, 2), size * 8.0 / 1024),
        space_used_mb = 
            convert(decimal(11, 2), fileproperty(name, 'spaceused') * 8.0 / 1024)
    from sys.database_files
)
select
    name,
    type_desc,
    physical_name,
    size_mb,
    space_used_mb,
    space_used_percent = 
        case size_mb
            when 0 then 0
            else convert(decimal(5, 2), space_used_mb / size_mb * 100)
        end
from db_file_cte;

Answer 2

This is normal behavior when you truncate table and which involves removal of more than 128 extents as Per Books Online

When you drop or rebuild large indexes, or drop or truncate large tables, the Database Engine defers the actual page deallocations, and their associated locks, until after a transaction commits. This implementation supports both autocommit and explicit transactions in a multiuser environment, and applies to large tables and indexes that use more than 128 extents.

The Database Engine avoids the allocation locks that are required to drop large objects by splitting the process in two separate phases: logical and physical.

In the logical phase, the existing allocation units used by the table or index are marked for deallocation and locked until the transaction commits. With a clustered index that is dropped, the data rows are copied and then moved to new allocation units created to the store either a rebuilt clustered index, or a heap. (In the case of an index rebuild, the data rows are sorted also.) When there is a rollback, only this logical phase needs to be rolled back.

The physical phase occurs after the transaction commits. The allocation units marked for deallocation are physically dropped in batches. These drops are handled inside short transactions that occur in the background, and do not require lots of locks.

Because the physical phase occurs after a transaction commits, the storage space of the table or index might still appear as unavailable. If this space is required for the database to grow before the physical phase is completed, the Database Engine tries to recover space from allocation units marked for deallocation. To find the space currently used by these allocation units, use the sys.allocation_units catalog view.

Deferred drop operations do not release allocated space immediately, and they introduce additional overhead costs in the Database Engine. Therefore, tables and indexes that use 128 or fewer extents are dropped, truncated, and rebuilt just like in SQL Server 2000. This means both the logical and physical phases occur before the transaction commits.

You would have to wait and of course you would have to manually shrink the file to reclaim space its also worth mentioning that shrinking causes logical fragmentation and should be avoided unless your need is grave. You would have to some how balance between shrinking and fragmentation. Its better to ask yourself whether shrink would actually solve issue considering the scenario where data will again grow anyhow.

Use below query to check how much free space is there in database

SELECT name ,size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS AvailableSpaceInMB
FROM sys.database_files;

Answer 3

In addition to Tom's and Shanky's answers, if your database contains LOB/BLOB data the DBCC SHRINKFILE may not work. If that is the case, then you have two options, depending on whether you can take the database offline or not. If you can take the database offline, then you will need to copy the data out and copy it back in to remove the empty space. You can accomplish this by one of the following:

1. Using a SELECT INTO statement to transfer the whole table to a new table. Drop the original table, run DBCC SHRINKFILE. Rename the new table to the original table name.
2. Using bcp to copy the table out in native mode, drop the table, run DBCC SHRINKFILE, create table, and then bcp the data into the table.
3. Using Export/Import to move all the data to a new database, drop existing database, rename new database to the original database name.

If you can't take the database offline, then you can use DBCC SHRINKFILE command with the EMPTYFILE option.

Details for offline copy: http://support.microsoft.com/kb/324432/en-us

Current information for EMPTYFILE option http://msdn.microsoft.com/en-us/library/ms189493(v=sql.105).aspx

Answer 4

To better understand why SQL doesn't automatically recover space when you delete data, think of SQL Server data files as a commercial self-storage unit:

Data from tables and indexes is organized into these units (called "pages"), and SQL server is very meticulous about keeping track of what belongs where.

If you fill up all the storage units, you can buy land and build more (expanding the data file), but what happens when some units get emptied?

• If the empty unit is at the end of the row, sure, you could demolish the unit you built and sell off the land, but that's a lot of work, especially if you might need to buy it right back again.
• But if the empty unit is in the middle of the row of storage units, you can't even do that without a lot of extra work. You'd have to move all the stuff from full units at the end of the row into the empty units in the middle before demolishing units and selling off the property.

Not only is moving all those units around a lot of (unnecessary) work, it also can cause table and index fragmentation, which can impact performance.

Imagine if you had a family that bought 10 units next to each other, and you later forced them to move 4 of those units to random empty units elsewhere in the facility. When they came to add or remove stuff from their units, they will have to do a lot of extra work wandering around between the scattered units.

Obviously this analogy isn't perfect, and clearly there are situations that you really do want to shrink a data file, but you don't want SQL server doing it on its own, you want to be in control of when and how exactly that happens.

For example, you'd probably want to do a DBCC SHRINKFILE to a specific size, leaving some portion of internal free space, then follow immediately by a reindex/defrag of your tables/indexes, to fix any fragmentation you've just created.

Moral of this story: Never turn on AutoShrink!