11

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

10
+50

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 |
+--------------------------------------------------+---------------------+-------------------------+
|improve this answer|||||
  • 1
    Or if the table is big then BCP out data and then BULK INSERT back in - during maintenance window. – Kin Shah Oct 31 '13 at 12:04
6

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

|improve this answer|||||
2

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.

|improve this answer|||||
1

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

  1. Add a new file to the filegroup.
  2. 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.
  3. Run DBCC SHRINKFILE(newfile,EMPTYFILE). This will move the data back, minus the excess space.
  4. 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.

|improve this answer|||||
-1

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

|improve this answer|||||
  • I've tried running: ALTER INDEX ALL ON [MyTableName] REORGANIZE ; – DermFrench Oct 28 '13 at 13:20
  • 3
    Rebuild it ;) Not reorganize. – TomTom Oct 28 '13 at 13:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.