Sql-server – How to compress a non-shrinkable table with nvarchar(max) fields

shrinksql serversql server 2014

We have a serious problem which was on our minds for pretty much two weeks already. We have a big database (~250 GB, MS SQL Server 2014) and we needed to shrink it somehow, since our SSD is almost full. Quick analysis showed that 1) there is almost no free space available in the database for shrinking operation, 2) we have two very big tables (~95 GB, ~5M entries each) in the database. The database is being used by external software (ERP system) and we had everything we needed to clean these tables from software side (these are tables are being used by internal journals and old entries are not necessary), so we did just that, which resulted in ~2,7M entries being deleted (and they actually were deleted, which was confirmed by a query).

But there is the problem – the actual size of the tables did not change. The table with 2,3M entries still use about ~94 GB of disk space, almost exactly as it was when there were 5M entries. The amount of detected free space for shrinking (both DB and files) also didn't change. I restored database from fresh backup on another server, and tried to rebuild, and then delete and restore clustered index, but later learned that it wouldn't work, since the table has two nvarchar(max) fields (these fields store XML data and internal statuses as strings). I also tried to execute the following query as an experiment:

use uvs_experiment;

select * into _InfoRg3380Tmp from _InfoRg3380 where 1=2;

truncate table _InfoRg3380Tmp;

insert into _InfoRg3380Tmp select * from _InfoRg3380 where _Fld3382RRef = 0x80C7A4BF011CE3C311E847800C55C223;

This query was an attempt to create a table which would store entries with the only one, actually the most meaningful status. And the result… was the same. The new target table still use about 90GB of space, with ~1,3M entries. Please note that almost all the entries in the table have some data (sometimes heavy) in nvarchar(max) fields, excluded entries is not an exception, i.e. it wasn't blank entries which was deleted earlier or excluded by this query.

Tables before and after

Just what is happening here and how we can free some space? I understand that this is some really strange behaviour of SQL Server regarding how it handles pages with nvarchar(max) fields, but we need a solution (aside from moving database to another server). Unfortunately, GZip compression from SQL Server 2016+ is not an option here.

Best Answer

We added compression for LOBs in CCIs exactly for this scenario in SQL 2016+. If you can't upgrade, then likely you should get more disk space for the server as the easiest fix.