Post reply

Reclaim space after setting varbinary(max) column to NULL

  • June 21, 2022 at 1:46 pm

    Go to Answer

    #4052733

    I have a sql server 2019 database with a table that contains  a varbinary(max) column . I have used SSIS package to move the content to a datawarehouse and update the varbinary(max) column to null. In SQL 2008R2 I was able to reclaim the space back after setting the varbinary(max) column to null by running  the sql script below which reduce the size of the database.

    DBCC CLEANTABLE (Mydatabase,'[dbo].[MyTable]', 0)  
    WITH NO_INFOMSGS;  
    GO

    I am running the same script in SQL Server 2019 and it doesn't work. The size of the database is not reducing.

    • This topic was modified 2 years, 5 months ago by  kdaniapam.

  • June 21, 2022 at 2:13 pm

    Answer

    #4052767

    That should reclaim space from that table back to the database itself.

    I think you'll still need to run SHRINKFILE to free the space the database itself back to the disk.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • June 21, 2022 at 2:20 pm

    #4052769

    Hi ScottPletcher,

    I was worried about fragmentation but I just read I can use DBCC SHRINKFILE with TRUNCATEONLY and it will not cause fragmentation. Am I on the right track with the above statement?

     

  • June 21, 2022 at 2:23 pm

    #4052770

    No, unfortunately you can't use WITH TRUNCATEONLY to free the space.

    Fragmentation is possible from the SHRINK, so you'll have to check if any tables need rebuilt after you've shrunk the file(s) involved.

    Yes, it's a pain.  Releasing space from a SQL Server database back to the disk is a royal pain.

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

  • June 21, 2022 at 2:25 pm

    #4052771

    Thanks. A lot

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply