Deallocation of Space from Image Column

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server 2008 Administration » Deallocation of Space from Image Column

Deallocation of Space from Image Column

Rate Topic

Display Mode

Topic Options

Author

Message

Michael Valentine Jones

Posted Wednesday, February 13, 2013 10:59 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 3:14 PM
Points: 2,941, Visits: 10,481

I have a table which has an image column that contains an average of 67,000 bytes/row with 32,000 rows giving a total of 2100 MB of data for that column.

I ran an update to set this column to null for rows older than 3 months old. The total bytes stored is now 884 MB for that column.

However, the total space allocated to this table was only reduced from 2.6 GB to 2.4 GB, even after I rebuilt the table.

I tried running DBCC CLEANTABLE, and that had no impact, as I expected, since I did not drop the column.

The indexes are not that large, and there are no indexes that include LOB columns.

If I copy the data from that table into a new table using SELECT INTO, the size of the new table is 1.1 GB, instead of the 2.4 GB in the current table.

Reloading the table is not an option, since I expect to perform a weekly or monthly purge to keep the size of this table under control, and this is a 24x7 web application.

Is there anything that I can do to reduce the data allocation to bring it in line with the amount of data stored?

Would SQL Server handle the deallocation better if I change the datatype of the column to varbinary(MAX)?

The version is Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 Enterprise Edition (64-bit)

Post #1419659

IgorMi

Posted Thursday, February 14, 2013 1:46 AM

SSCommitted

Group: General Forum Members
Last Login: Yesterday @ 1:06 PM
Points: 1,861, Visits: 1,422

Hi,
You should change the data type from image to varbinary(max). Image LOB data is stored off-row (complex pointing), and varbinary(max) is stored in-row (part of the data and index)

IgorMi

Post #1419869

« Prev Topic | Next Topic »

Permissions