Sample Header Ad - 728x90

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 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: enter image description here 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 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