Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a table in SQL Server 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
share|improve this question
    
What version of SQL Server Express? –  Max Vernon Oct 28 '13 at 16:57
    
SQL Server 2012 Express –  DermFrench Oct 29 '13 at 10:50
    
Did you get a chance to try the LOB_COMPACTION option from my answer? –  Max Vernon Oct 29 '13 at 12:46
    
Get following 'LOB_COMPACTION' is not a recognized ALTER INDEX REBUILD option. –  DermFrench Oct 29 '13 at 12:56
    
Hmmm upon further reading I see LOB_COMPACTION is only valid for REORGANIZE operations, and is ON by default, so that is not the issue at all. Sorry for that wild goose chase. –  Max Vernon Oct 29 '13 at 13:10
show 6 more comments

5 Answers 5

up vote 3 down vote accepted
+50

You say in a comment

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 

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 |
+--------------------------------------------------+---------------------+-------------------------+
share|improve this answer
    
I don't know how comfortable I'd be using a temp table... What if the machine crashes or something at the wrong moment? Would a table in a new, dedicated database be better? –  Max Vernon Oct 31 '13 at 3:39
    
Yeah I think a script to bring the data into a new database with same table and then reimport would be safer, if someone wants to add that as an answer I'll accept it. –  DermFrench Oct 31 '13 at 10:30
1  
@MaxVernon - 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 though. Also it should really lock [dbo].[MyTableName] at the beginning to prevent any concurrent activity. –  Martin Smith Oct 31 '13 at 10:32
    
@DermFrench - See comment above. –  Martin Smith Oct 31 '13 at 10:32
1  
@DermFrench - Regarding the locking I added a SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; anyway. This will prevent any concurrent modifications from happening during or after the copy and being lost. –  Martin Smith Oct 31 '13 at 11:11
show 4 more comments

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

share|improve this answer
    
I'll try this out and see if it works. As a matter of interest if I don't do this will SQL Server ever free that space? –  DermFrench Oct 28 '13 at 13:37
    
I've tried ALTER INDEX PK_Image ON MyTableName REBUILD WITH (ONLINE = OFF) and it still doesn't free up any space? –  DermFrench Oct 28 '13 at 13:43
    
Any new rows inserted into the table will be stored in the unused portion of the space allocated for the table. –  Max Vernon Oct 28 '13 at 13:43
    
I've modified my answer with further details. –  Max Vernon Oct 28 '13 at 14:34
    
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). –  DermFrench Oct 28 '13 at 14:44
add comment

make sure the DB recovery mode is simple.

alter the column as VARBINARY(MAX).

then try copying the data in to completely new table.

check the new table size using sp_spaceused "tablename" if you satisfied with unused space of table then check the unused space of DB using same commond w/o specifying table name, that space is still with DB and not release to OS.

you can drop original table, rename the new table or do 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 file and release unused space.

share|improve this answer
    
I considered that as well but as we don't know if ImageID is referenced by any FKs decided to go for the UPDATE approach. –  Martin Smith Oct 31 '13 at 11:17
    
I am sorry, but I didn't get your answer. Why can't we check what FK are there, make a note of that, drop them, performs the task I mentioned and recreate the FK. Of course this will take time and this operation should be done at off time. This whole task can be done through script as well to let it run over night. Am I missing anything here? –  Anup Shah Oct 31 '13 at 13:14
    
No that would work. Plenty of ways to skin this cat! –  Martin Smith Oct 31 '13 at 13:16
add comment

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

share|improve this answer
    
What t-sql do I run to do that? –  DermFrench Oct 28 '13 at 13:15
    
I've tried running: ALTER INDEX ALL ON [MyTableName] REORGANIZE ; –  DermFrench Oct 28 '13 at 13:20
2  
Rebuild it ;) Not reorganize. –  TomTom Oct 28 '13 at 13:34
add comment

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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