How to free the unused space for a table
10
votes
2
answers
20753
views
This question is asked like tens of times, and to my surprise such a simple requirement comes so difficult. Yet I can't solve this problem.
I use SQL Server 2014 Express edition with 10GB limit of database size (not a filegroup size, database size).
I crawled news, and inserted HTML into a table. Table's schema is:
Id bigint identity(1, 1) primary key,
Url varchar(250) not null,
OriginalHtml nvarchar(max),
...
Database ran out of size and I received
As I understand this picture the unused space is now like 50 percent of total size. That is, now I have 5GBs unused space. But I can't reclaim it.
Rebuilding indexes didn't help. The
insufficient disk space
Of course shrinking database and filegroup didn't help. DBCC SHRINKDATABASE
didn't help. So I wrote a simple application to read each record, strip out some unwanted parts of the OriginalHtml
like head section and aside and footer to keep the main body only and I now see this image when getting report of disk usage by top tables:

truncateonly
option won't help because as I understood no record is deleted, only the size of each record is reduced.
I'm stuck at this point. Please help, what should I do?
Clustered Index is on column Id
.
This is the result of EXECUTE sys.sp_spaceused @objname = N'dbo.Articles', @updateusage = 'true';
name rows reserved data index_size unused
----------- -------- ------------ ----------- ------------ -----------
Articles 112258 8079784 KB 5199840 KB 13360 KB 2866584 KB
Asked by Saeed Neamati
(1515 rep)
Sep 26, 2017, 04:34 AM
Last activity: Mar 17, 2023, 09:57 AM
Last activity: Mar 17, 2023, 09:57 AM