Post reply

NULLing Out Column of Image Data Type

  • February 3, 2012 at 4:07 pm

    #250278

    Hey SSC Comm,

    I have a question for you all, I have a table with 6mil records of that, 250k have values in a column that is of the data type Image.

    I'm looking to reclaim the space that it's tying up. I don't want to purge the records, just get rid of the image data.

    If I read correctly the data is held off-row and the actual on-row data is just text pointers.

    I went through and ran an update on the column NULLing out those records but didn't see any real return in database free space.

    Is there something I'm missing?

    I've tried the following:

    1) Drop the clustered index and rebuild it.

    2) DBCC CLEANTABLE

    3) DBCC UPDATEUSAGE

    Any insight on this would be awesome.

    Thanks!

  • February 3, 2012 at 5:08 pm

    #1442697

    Unless you have auto-shrink enabled (not recommended) the data file won't change size. It should be using less space within the data file but the actual file size won't change. Have you tried to shrink the file via SSMS or DBCC? Remember that the image data is not necessarily stored in the same file group as the table data.

  • February 3, 2012 at 6:40 pm

    #1442708

    Hi Hendricks,

    I should have mentioned that, I don't see less space being used within the primary data file. I understand I wouldn't reclaim the space to the OS until running a DBCC SHRINKFILE.

    I talked with SQLRNNR and decided that the best option is to script out to a new table, populate it passing a null value for the image data type column, and then script and rebuild all indexes, contraints, triggers, and keys and finally perform a rename original to old, new to original... test and drop the old. Shrink the data file and rebuild indexes across the DB.

    From what I'm told a clustered index rebuild should have been the trick but I'm not sure why it didn't when I tried.

    Thanks!

  • February 6, 2012 at 8:02 am

    #1443163

    Try ALTER INDEX ... REORGANISE (not rebuild, rebuild doesn't do anything to the LOB pages) and use the LOB_COMPACTION option.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

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

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