SQL Server – Truncated 200GB Table but Disk Space Not Released

sql serversql-server-2012truncate

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.

Best Answer

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;
Related Question