SQL Server Performance Forum – Threads Archive

Deleting Blob Data from tables

I have been that if you delete blob data from a table that it actually does not get deleted. Is it true if you delete the blob then run the shrik command this release the space?

As far as I understood it always works that way unless in the database options AutoShrink is enabled. This keeps the server from always de- an reallocating disk space which would unnecessarily cost performance.

AUTOSHRINK will not work always, it is better to perform SHRINFILE explicitly.
If you’re going to use that space in database then better to leave as it is, perform regular update statistics in order to catchup the correct information. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

There is a flaw in SQL Server 2000. When you delete large amounts of BLOB data from a table, the space is not automatically freed. You need to BCP the data out and back in in order to reclaim that space.
Check this out:http://support.microsoft.com/default.aspx?scid=kb;en-us;324432 This is supposed to be fixed in SQL Server 2005. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Heute schon gebloggt?http://www.insidesql.de/blogs
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

]]>