none
UPDATE Varbinary(max) to NULL cannot reclaim space

    Question

  • My production database stores blob data (photo, binary files, etc.) in a table with a varbinary(max) column. The blob data only needed to retain for 3 months, after the retention period the blob data can be purged to reclaim disk space, but data in other columns of the same table must be kept permanently. So I try to use UPDATE statement to set the varbinary(max) column to NULL, but seems the space cannot be reclaimed. Below is a simplified SQL scripts to demonstrate what I wanna do and the outcome of it:

    CREATE TABLE [dbo].[TestBlob](
        [pk] [int] NOT NULL PRIMARY KEY,
        [blob] [varbinary](max) NULL
    )
    GO

    TRUNCATE TABLE TestBlob;

    SELECT 'EMPTY'
    SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
    EXEC sp_spaceused @updateusage = N'TRUE'
    EXEC sp_spaceused 'TestBlob';

    SET NOCOUNT ON;
    DECLARE @i int = 1
    WHILE @i < 100000
    BEGIN
    INSERT TestBlob (pk, blob) SELECT @i, CONVERT(varbinary(max), '
    asdafdfdsfdsfdsfdfsdfsdgfgdfghghfjgfhjgkgjkjhkhlkljkljkljklkjljkljkljkljlkjlkkkkkkkkkkkkkkkkkkkkkkkkkkkkkjaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12113433aaaaaaaaaaaaaaaaaaaaaaaaa
    asdafdfdsfdsfdsfdfsdfsdgfgdfghghfjgfhjgkgjkjhkhlkljkljkljklkjljkljkljkljlkjlkkkkkkkkkkkkkkkkkkkkkkkkkkkkkjaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa12113433aaaaaaaaaaaaaaaaaaaaaaaaa
    ')
    SET @i +=1
    END

    SELECT 'FULL'
    SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
    EXEC sp_spaceused @updateusage = N'TRUE'
    EXEC sp_spaceused 'TestBlob';

    UPDATE TestBlob SET blob = NULL;

    SELECT 'UPDATE NULL';
    SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
    EXEC sp_spaceused @updateusage = N'TRUE'
    EXEC sp_spaceused 'TestBlob';

    DELETE TestBlob;

    SELECT 'DELETE';
    SELECT blob, COUNT(*) AS cnt FROM TestBlob GROUP BY blob;
    EXEC sp_spaceused @updateusage = N'TRUE'
    EXEC sp_spaceused 'TestBlob';

    As you can see, even after I updated the varbinary(max) column to NULL, the data size of the table still didn't drop.
    The data size dropped only after I deleted the rows, but that's not what I need.

    Can anyone tell me how to make it?

    Wednesday, April 11, 2018 5:40 AM

Answers

All replies