O caso da tabela VAZIA de 70 Gb

 USE SANDBOX GO SET NOCOUNT ON  GO IF OBJECT_ID('TABLE_CLUSTER') IS NOT NULL DROP TABLE TABLE_CLUSTER GO  CREATE TABLE TABLE_CLUSTER ( ID INT IDENTITY(1,1) PRIMARY KEY ,COL VARCHAR(8000) ) GO ---------------------------------------------------------------------------------------------- --INSERT 1 ROW ---------------------------------------------------------------------------------------------- INSERT INTO TABLE_CLUSTER VALUES (REPLICATE('A',8000)) GO SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_CLUSTER') ) GO EXEC sp_spaceused 'TABLE_CLUSTER' GO ---------------------------------------------------------------------------------------------- --INSERT +1000 ROWs ---------------------------------------------------------------------------------------------- INSERT INTO TABLE_CLUSTER VALUES (REPLICATE('B',8000)) GO 5000  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_CLUSTER') ) GO EXEC sp_spaceused 'TABLE_CLUSTER' GO  ---------------------------------------------------------------------------------------------- --DELETE ROWS 1 by 1 ---------------------------------------------------------------------------------------------- DELETE TOP (1) FROM TABLE_CLUSTER GO 5001  WAITFOR DELAY '00:00:05' -- WAITING FOR ASYNC CLEAN UP (*GHOST CLEAN UP) GO  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_CLUSTER') ) GO EXEC sp_spaceused 'TABLE_CLUSTER' GO

 USE SANDBOX GO SET NOCOUNT ON  GO IF OBJECT_ID('TABLE_HEAP') IS NOT NULL DROP TABLE TABLE_HEAP GO  CREATE TABLE TABLE_HEAP ( ID INT IDENTITY(1,1)-- PRIMARY KEY ,COL VARCHAR(8000) ) GO ---------------------------------------------------------------------------------------------- --INSERT 1 ROW ---------------------------------------------------------------------------------------------- INSERT INTO TABLE_HEAP VALUES (REPLICATE('A',8000)) GO SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_HEAP') ) GO EXEC sp_spaceused 'TABLE_HEAP' GO ---------------------------------------------------------------------------------------------- --INSERT +1000 ROW ---------------------------------------------------------------------------------------------- INSERT INTO TABLE_HEAP VALUES (REPLICATE('B',8000)) GO 5000  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_HEAP') ) GO EXEC sp_spaceused 'TABLE_HEAP' GO  ---------------------------------------------------------------------------------------------- --DELETE ROWS 1 by 1 ---------------------------------------------------------------------------------------------- DELETE TOP (1) FROM TABLE_HEAP GO 5001  WAITFOR DELAY '00:00:05' -- (*THERE IS NO GHOST CLEAN UP FOR HEAP) LET IT HERE JUST TO BE LIKE CLUSTER SCRIPT GO  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_HEAP') ) GO EXEC sp_spaceused 'TABLE_HEAP' GO

DELETE (Transact-SQL)

https://msdn.microsoft.com/en-us/library/ms189835.aspx

Locking Behavior

By default, a DELETE statement always acquires an exclusive (X) lock on the table it modifies, and holds that lock until the transaction completes. With an exclusive (X) lock, no other transactions can modify data; read operations can take place only with the use of the NOLOCK hint or read uncommitted isolation level. You can specify table hints to override this default behavior for the duration of the DELETE statement by specifying another locking method, however, we recommend that hints be used only as a last resort by experienced developers and database administrators. For more information, see Table Hints (Transact-SQL).

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 an exclusive 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.

 ------------------------------------------------------------------------------------------------SOLUTION---------------------------------------------------------------------------------------------- ALTER TABLE TABLE_HEAP REBUILD  SELECT total_pages, used_pages, data_pages FROM sys.allocation_units where container_id = ( SELECT partition_id FROM sys.partitions where object_id = object_id('TABLE_HEAP') )  GO  EXEC sp_spaceused 'TABLE_HEAP'  GO