Freeing Unused Space SQL Server Table
The only thing we have done is replaced
ScanImageon every row with a much smaller image (this is how so much unused space is there)
From doing some experimentation the most space effective method would be to drop the allocation unit and repopulate it (if you have a maintenance window to do this in).
Example code that achieved the best space reduction for me with the table structure in the question is:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET XACT_ABORT ON;
BEGIN TRAN
SELECT [ImageID],
[ScanImage]
INTO #Temp
FROM [dbo].[MyTableName]
ALTER TABLE [dbo].[MyTableName]
DROP COLUMN [ScanImage]
/*Allocation unit not removed until after this*/
ALTER INDEX PK_Image ON MyTableName REBUILD
ALTER TABLE [dbo].[MyTableName]
ADD [ScanImage] IMAGE NULL
UPDATE [dbo].[MyTableName]
SET [ScanImage] = T.[ScanImage]
FROM [dbo].[MyTableName] M
JOIN #Temp T
ON M.ImageID = T.[ImageID]
DROP TABLE #Temp
COMMIT
Everything is in a transaction so if the machine crashes it will be rolled back. Could probably do with some error handling or at least SET XACT_ABORT ON. I used SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; to prevent any concurrent modifications from happening during or after the copy and being lost.
The number of LOB pages reserved after reducing the size of an image in all rows was as follows:
+--------------------------------------------------+---------------------+-------------------------+ | Event | lob_used_page_count | lob_reserved_page_count | +--------------------------------------------------+---------------------+-------------------------+ | Inserted 10,000 rows with 100,000 byte data each | 135005 | 135017 | | Updated all rows to 10,000 byte image data | 31251 | 135012 | | Reorganize | 23687 | 25629 | | Drop and re-add image data | 13485 | 13489 | +--------------------------------------------------+---------------------+-------------------------+
Try
ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF)
This recreates the clustered index, so you will need extra room in your database for the operation to complete. If you don't have any extra room because your disk is full, you could possibly add a new data file to the database (on a different disk) and move the table to it.
It is also possible the clustered index is define with a FILLFACTOR less than 100%. Having the fill factor set to, for instance 66%, would leave 1/3 of each data page empty for future use. If this is the issue you could modify the fill factor using ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF, FILLFACTOR=100)
If you have recently dropped a variable length field from the table, you could also try DBCC CLEANTABLE( Databasename, "MyTableName")
Books online (BOL) has a great article on rebuilding indexes at http://technet.microsoft.com/en-us/library/ms188388%28v=sql.100%29.aspx
Make sure the DB recovery mode is SIMPLE.
alter the column as VARBINARY(MAX).
Then try copying the data into a completely new table.
Check the new table size using sp_spaceused "tablename". If you are satisfied with the unused space of table, then check the unused space of the database using the same command without specifying a table name. That space is still within the database files and not released to the OS.
You can drop the original table and rename the new table, or do the same thing again, and use original table name if you do not trust renaming operation, (I do not trust completely).
If this works then last step is easy: You know how to shrink files and release unused space.
If there are any foreign keys, record their definitions, drop them, perform the tasks I mention above, and recreate the foreign keys afterward. Of course this will take time and this operation should be done during off times. This whole task can be done through script as well to let it run overnight.