I have a table in SQL Server 2012 Express with a lot of unused space.
I need to free up space in the database.
| NAME | ROWS | RESERVED | DATA | INDEX_SIZE | UNUSED | |-------------|--------|--------------|--------------|------------|--------------| | MyTableName | 158890 | 8928296 KB | 5760944 KB | 2248 KB | 3165104 KB |
How do I get SQL to release the 3165104KB?
I've already tried:
Alter table MyTableName Rebuild
DBCC CLEANTABLE (MyDbName,"MyTableName ", 0)
ALTER INDEX ALL ON MyTableName REORGANIZE ;
ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF)
Here is the table:
CREATE TABLE [dbo].[MyTableName](
[ImageID] [int] IDENTITY(1,1) NOT NULL,
[DateScan] [datetime] NULL,
[ScanImage] [image] NULL,
CONSTRAINT [PK_Image] PRIMARY KEY CLUSTERED
(
[ImageID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 100) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
The only thing we have done is replaced ScanImage
on every row with a much smaller image (this is how so much unused space is there).
Best Answer
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:
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 usedSET 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: