Unable to relcaim disk space after deleting the records from tables that has column QUERY_BLOB

Question

Unable to relcaim disk space after deleting the records from tables that has column QUERY_BLOB

Kranthi DBA 221

We are seeing an issue wherein millions of records are deleted from the tables that have column QUERY_BLOB(data type ntext), but the table usage is still the same. We have shrunk the files, rebuild indexes using the offline option on those tables, but still unable to see any free space that is released.

Ex: Table1 has totalspaceMB as 50000 MB.

Even after deleting the records, the totalspaceMB is the same.

Please advise if any one has come across this issue and how to fix it.

Thanks in Advance!

5 answers

Your answer

Answer 1

Olaf Helper 47,516

Is that a Heap Table = table without a clustered index?
Then you can rebuild the table to get back the unused space. During rebuild the complete table is locked and if it fails there is a risk of lossing data, so better test it before you do it in prod.

ALTER TABLE dbo.YourTable WITH REBUILD;

Kranthi DBA 221 Reputation points

2021-06-04T14:48:19.877+00:00

There are couple of tables, one table with a clustered index and other with a non-clustered index. I have already run index rebuild with offline option on the table with the clustered index, but still the space doesn't get released.
Kranthi DBA 221 Reputation points

2021-06-04T17:43:32.443+00:00

Just to provide additional details, SQL version is 2017. I am seeing in the below link that the "ntext" datatype is deprecated in SQL 2017. Please advise if that is correct and what needs to be done as a workaround to resolve the issue.

https://learn.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-server-2017?view=sql-server-ver15

Please let me know the procedure to be followed to reclaim disk space from the table.
Olaf Helper 47,516 Reputation points

2021-06-07T06:43:58.967+00:00

"ntext" datatype is deprecated in SQL 2017

Exactyl it's deprecated since version 2005; 16 years later it's still available ...

There is a know issue with space when you drop a column, which isn't the case here. That can be fixed with DBCC CLEANTABLE (Transact-SQL)

Answer 2

Erland Sommarskog 121.9K MVP Volunteer Moderator

You may want to try ALTER INDEX REORGANIZE which does LOB_COMPACTION by default. REBUILD does not do that.

Beware through that shrink operations can run very slow when you have LOB data, so you may end up with having to copy the data to a new database.

Kranthi DBA 221 Reputation points

2021-06-05T06:20:25.267+00:00

I did try ALTER INDEX REORGANIZE as well on the indexes and there was no space that got released even after that.
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator

2021-06-05T08:41:17.157+00:00

Exactly what space was not released? The only way the size of the data file can be reduced is by an explicit shrink operation.

The space allocated for the table may be reduced though. I say "may", because this is kind of a sore point, and in the end copying all data over to a new database, or at least a new table may be the only solution.

And if you take this route, make sure that you use nvarchar(MAX) in the new table.
Kranthi DBA 221 Reputation points

2021-06-05T11:56:57.447+00:00

The total space in MB is value is seen as 50000 MB and unused space is just 12 MB.
Millions of records were deleted from the application end after which we have reorganized and rebuilt the indexes, but still space doesn't get reclaimed.
The "Total Space in MB" and unused space value for the tables with BLOB data is still the same, so I want to know what needs to be done to reclaim space.
Please advise.

Answer 3

Hello,

After clearing a large amount of data through the delete operation, rebuild or create a new clustered index on the table to rearrange the data pages, which can effectively release the pages used by the table.

But for a table that contains LOB data types (image, ntext, text), SQL Server uses a separate page to store these data. Index rebuilding will not affect them, and you may need to rebuild the table.

Official documents (ntext, text, and image (Transact-SQL)) have stated:

IMPORTANT! ntext, text, and image data types will be removed in a future version of SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead.

Answer 4

Alexander Schäpper 96

I ran this and it worked like a charm:

DBCC SHRINKDATABASE (N'yourdatabase');

According:
https://learn.microsoft.com/en-us/azure/azure-sql/database/file-space-manage#reclaim-unused-allocated-space

Answer 5

Tom Phillips 17,771

See:

https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-cleantable-transact-sql?view=sql-server-ver15

Share via

Unable to relcaim disk space after deleting the records from tables that has column QUERY_BLOB

5 answers

Your answer