How to compress a non-shrinkable table with nvarchar(max) fields?

Question

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.

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.

Answer 1

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.

Answer 2

Answer to an old question, but starting with SQL 2016 there are also the COMPRESS() / DECOMPRESS() functions. If you can't change the code of the app but are allowed to change the schema, you could

• create a new table with _compressed-suffix
• insert all existing rows into it but use COMPRESS() for the max-column(s)
• drop the original table
• create a view with the original name which calls DECOMPRESS() to return the original content
• create a instead of insert / update trigger onto the view which redirects the insert / update into the _compressed table AND uses COMPRESS() again

Drawback:

• slower write performance
• more CPU usage
• compares (equal or like) onto the compressed columns will be very slow (it has to decompress every single row)
• problems with your vendor, when he tries to change the table while implementing a new version

Theoretical you could do this for even before SQL 2016, when you are using clr stored procedures to compress / decompress (CLR = external DLLs (e.g. C#)), but this would have even more drawbacks.