7

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 
9

All things being equal, it should be enough to compact the large object (LOB) column OriginalHTML. You don't specify the clustered index name in the question, so:

ALTER INDEX ALL
ON dbo.Articles
REORGANIZE 
WITH (LOB_COMPACTION = ON);

See ALTER INDEX (Transact-SQL)

If you have the clustered index name (not just the clustered column(s)), replace the ALL above with that name.

The LOB_COMPACTION option defaults to ON, but there's no harm in being explicit. You may need to run the REORGANIZE repeatedly to finish reclaiming all the unused space.

Unfortunately, the way LOB data is organized and the way LOB compaction is implemented means this method may not always be able to reclaim all the unused space, no matter how many times you run it. It can also be very slow.

You could also try the method in the related Q & A Freeing Unused Space SQL Server Table

If, for whatever reason, the above does not work for you, export the data to a file, truncate the table, then reload it. There are several methods to achieve that, for example the bcp utility.

Example

The following creates a table with 10,000 wide rows:

CREATE TABLE dbo.Test 
(
    c1 bigint IDENTITY NOT NULL, 
    c2 nvarchar(max) NOT NULL,

    CONSTRAINT PK_dbo_Test
        PRIMARY KEY CLUSTERED (c1)
);

-- Load 10,000 wide rows
INSERT dbo.Test WITH (TABLOCKX)
    (c2)
SELECT TOP (10000)
    REPLICATE(CONVERT(nvarchar(max), 'X'), 50000)
FROM master.sys.columns AS C1
CROSS JOIN master.sys.columns AS C2;

We can see the space usage using the sys.dm_db_index_physical_stats DMV:

SELECT
    DDIPS.index_id,
    DDIPS.partition_number,
    DDIPS.index_type_desc,
    DDIPS.index_depth,
    DDIPS.index_level,
    DDIPS.page_count,
    DDIPS.avg_page_space_used_in_percent
FROM sys.dm_db_index_physical_stats
(
    DB_ID(),
    OBJECT_ID(N'dbo.Test', N'U'),
    1,
    NULL,
    'DETAILED'
) AS DDIPS
WHERE 
    DDIPS.alloc_unit_type_desc = N'LOB_DATA';

DMV output

We now update the LOB content to a smaller size (but one that still requires off-row storage):

-- Change LOB data to a smaller value (that will not move in-row)
UPDATE dbo.Test WITH (TABLOCKX)
SET c2 = REPLICATE(CONVERT(nvarchar(max), 'Y'), 5000);

DMV output

Notice that some space has been reclaimed, but the remaining pages are much less full than they were.

We can compact the LOB space using:

ALTER INDEX PK_dbo_Test ON dbo.Test 
REORGANIZE 
WITH (LOB_COMPACTION = ON);

DMV output

This results in some compaction and space savings, but it is not perfect. Running the compaction again may or may not improve the situation. In my test, it did not, no matter how many times I re-ran it.

Export, truncate, reload

One way to do this entirely from Management Studio involves using xp_cmdshell to export the table data to a file. If xp_cmdshell is not currently enabled, the following will do that:

-- Enable xp_cmdshell if necessary
EXECUTE sys.sp_configure
    @configname = 'show advanced options',
    @configvalue = 1;

RECONFIGURE;

EXECUTE sys.sp_configure
    @configname = 'xp_cmdshell',
    @configvalue = 1;

RECONFIGURE;

Now we can perform the export:

-- Export table
EXECUTE sys.xp_cmdshell
    'bcp Sandpit.dbo.Test out c:\temp\Test.bcp -n -S .\SQL2017 -T';

Note you will need to change the path and -S server name, and possibly provide login credentials.

How we can truncate the table, and reload it using BULK INSERT:

-- Truncate
TRUNCATE TABLE dbo.Test;

-- Switch to BULK_LOGGED recovery model if currently set to FULL
-- Bulk load
BULK INSERT dbo.Test
FROM 'c:\temp\Test.bcp' 
WITH 
(
    DATAFILETYPE = 'widenative', 
    ORDER (c1), 
    TABLOCK,
    KEEPIDENTITY
);

The final step is to reset the identity seed:

-- Check and reseed identity
DBCC CHECKIDENT('dbo.Test', RESEED);

This sequence of operations is typically faster than LOB compaction, and should always produce optimal results:

DMV output

The above is not quite as efficient as it could be due to a long-standing bug: BULK INSERT with IDENTITY column creates query plan with SORT. The workaround listed there is effective, but I would only bother with it if the table is very large.

Don't forget to delete the temporary file used to hold the exported data.

You are of course free to use whichever bulk export/import approach is most convenient to you. It is not required to use xp_cmdshell or bcp.

Additional notes:

  • FILLFACTOR only applies to index pages. It does not affect off-row LOB storage (which is not stored on index pages).
  • Row and page compression are not available for off-row storage.
  • As an alternative, you can compress and decompress data explicitly using the COMPRESS and DECOMPRESS functions available from SQL Server 2016.

    An option for those using SQL Server 2014 (which is the case here) or older (down to SQL Server 2005) to get the same compression functionality provided by the COMPRESS and DECOMPRESS built-in functions is to use SQLCLR. Pre-built functions that do just this are available in the Free version of SQL# written by Solomon Rutzky. The Util_GZip and Util_GUnzip functions should be equivalent to COMPRESS and DECOMPRESS, respectively. And, anyone using SQL Server 2012 or newer should make sure that the server running SQL Server is updated with .NET Framework version 4.5 or newer so that the much improved compression algorithm will be used.

1

If you can upgrade to SQL Server Express 2016 SP1 or later, you can get a tremendous amount of space savings by using DATA COMPRESSION.

You may have other things in play that are bloating your database, However, as Dan Guzman's comment suggests, you should check the fill factor on all of your indexes.

Anything other than either 0 (zero) or 100 means that, when the index was created (or rebuilt), SQL Server only filled each page up to the percentage of the fill factor. So, for example, if you had a fill factor of 50, only 50 percent of the page would be filled during index creation/rebuild which would basically double the amount of space required to actually hold the data.

Pulling a query from the post Find Fill Factors for Indexes in a SQL Server Database

If you want to find all indexes for all user tables in a SQL Server database that have a fill factor different than 0 or 100:

SELECT DB_NAME() AS Database_Name
, sc.name AS Schema_Name
, o.name AS Table_Name
, o.type_desc
, i.name AS Index_Name
, i.type_desc AS Index_Type
, i.fill_factor
FROM sys.indexes i
INNER JOIN sys.objects o ON i.object_id = o.object_id
INNER JOIN sys.schemas sc ON o.schema_id = sc.schema_id
WHERE i.name IS NOT NULL
AND o.type = 'U'
AND i.fill_factor not in (0, 100)
ORDER BY i.fill_factor DESC, o.name

Additional valuable information related to fill factor can be found at

5 Things About Fillfactor

Blitz Result: Fill Factor (%)

  • 2
    An important note about DATA_COMPRESSION. Compression will not compress data that is stored off-row, such as LOB data or ROW_OVERFLOW data. In this case, only the ID & URL columns would compress--probably not significant savings. However, 2016 also introduces the COMPRESS() function, which would allow the OP to use the gzip algorithm to compress the OriginalHTML data in the table. – AMtwo Sep 26 '17 at 15:34
  • Thank you for that query. I executed it, and no resultset was returned. So, no fill factor other than 0 or 100. – Saeed Neamati Sep 26 '17 at 15:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.