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.
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 21, 2022 at 2:20 pm
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
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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
June 21, 2022 at 2:25 pm
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