Freeing Unused Space SQL Server Table

Question

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).

score 12 · Accepted Answer · edited Nov 24 '16 at 10:55

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)

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 |
+--------------------------------------------------+---------------------+-------------------------+

Hannah Vernon · Answer 2 · 2013-10-29T13:11:22.987

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

score 2 · Answer 3 · edited Nov 24 '16 at 11:03

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.

score 1 · Answer 4 · edited Nov 24 '16 at 11:06

I would just create a new database and copy the data to it. You should be able to use the import/export wizard. (Obviously a backup and restore would keep the problem.) Check out the results of importing the data. If all looks good rename the original database and then rename the new database to the name you want to use. (I always wait a bit before dropping the original, just to have a double-check online.)

For what is it worth, we have also reclaimed blob space from databases, if they are not too large, by the following steps. (However, since you are using SQL Server Express, you may not have room to try this.)

Add a new file to the filegroup.
Run DBCC SHRINKFILE(file, EMPTYFILE). Since you are shrinking the MDF it will eventually fail, since the system metadata cannot be moved. However, the empty blob allocations are not moved.
Run DBCC SHRINKFILE(newfile,EMPTYFILE). This will move the data back, minus the excess space.
Drop the new file (now empty) from the filegroup.

This eliminates the blob bloat. I should mention that we have used this technique primarily to create a mostly empty database for testing upgrade scripts.

score 0 · Answer 5 · answered Jan 30 '24 at 08:18

In SQL Server 2022 I tried a lot of different approaches but the only one that actually made the db-file shrink in the file system was to open the SQL Management Studio, right-click the database with excess data and select "task > shrink".

I did this after having done almost everything else that was suggested, such as altering the table and running different SP:s. So it could be a combination, but while some other SP:s spent hours working on the data, the task > shrink was the only one that finally altered the file size.

score -1 · Answer 6 · answered Oct 28 '13 at 12:47

-1

Reorganize the clustered index - that one has the data at the nodes, so.... it is likely fragmented.

answered Oct 28 '13 at 12:47

TomTom

4,636
1
19
20

Freeing Unused Space SQL Server Table

6 Answers6

Linked