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

Question

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

@GMB truncate would not be an option in production because we cannot delete all the data. Are you suggesting to move the rows we want to keep into a new table, and truncate the old? I guess that works. — Allen H., Jan 20 '20 at 22:17
This may help: https://serverfault.com/questions/55027/what-prevents-a-ms-sql-server-database-from-shrinking — Alex, Jan 20 '20 at 22:50
Don't Shrink. Most experts I know recommend not to shrink the database. — Zohar Peled, Jan 22 '20 at 19:09

lptr · Accepted Answer · 2020-01-21T10:52:00.390

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;

Thanks, this worked. Our table was a heap, so when I followed suggestions to reindex, nothing happened. The solution was to create a clustered index on the table, and that dropped the 60gb from the filesystem! — Allen H., Jan 21 '20 at 17:29

score 0 · Answer 2 · answered Jan 21 '20 at 05:02

0

right click on your db.click on properties then files. Notice the initial size log file.

answered Jan 21 '20 at 05:02

Reza Yousefi

148
11

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

2 Answers2