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.

  • I found some traces of Replication on database, this increase the log size dramatically and prevent to delete or shrink. Dec 12 '19 at 20:31

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;

;with db_file_cte as
        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
    space_used_percent = 
        case size_mb
            when 0 then 0
            else convert(decimal(5, 2), space_used_mb / size_mb * 100)
from db_file_cte;

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;

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

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.