SQL Server Performance

Deleting Blob Data from tables

Discussion in 'General DBA Questions' started by Laura Cunningham, Nov 17, 2005.

  1. Laura Cunningham New Member

    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?
  2. ralph New Member

    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.
  3. satya Moderator

    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.
  4. FrankKalis Moderator

    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)

Share This Page