[SOLVED] SQL Server - Deleted all rows in table (not truncate) but space is not decreasing after shrink

SQL Server - Deleted all rows in table (not truncate) but space is not decreasing after shrink

I was doing some testing on our UAT environment so I deleted all the rows in a big table. Then I ran a shrink on the affected file names.

However, I am still seeing the original size taken up by the table (60gb), even though there are 0 rows. Upon a further look, there is a NULL index (i think this means non-indexed, so the PK is used) taking up 30gb and 30gb of "free space" for this table.

How can I get both the index space and "Free space" back to 0gb?

Thanks! Allen

Solution

if your table is a heap, then space is not reclaimed when rows get deleted. You'll have to create a clustered index to reclaim the space (and drop the clustered index afterwards to have a heap at the end)

create table dbo.myheap
(
id int identity,
col char(500) not null
);
go


insert into dbo.myheap(col)
select top (10000) a.name
from master.dbo.spt_values as a
cross join master.dbo.spt_values as b;
go


exec sp_spaceused 'dbo.myheap' --myheap 10000       5384 KB 5336 KB 8 KB    40 KB
go

--delete all rows
delete dbo.myheap;
go

--space is not freed
exec sp_spaceused 'dbo.myheap' --myheap 0           5384 KB 5336 KB 8 KB    40 KB
go


--from heap to clustered
create clustered index clxheaptocluster on dbo.myheap(id);
go

exec sp_spaceused 'dbo.myheap' --myheap 0           0 KB    0 KB    0 KB    0 KB
go

--cleanup
drop table dbo.myheap
go

For clustered tables, rebuild the clustered index (or ALL):

ALTER INDEX ALL /*clusteredindexname*/ ON dbo.myclusteredtable REBUILD;