I have a table without a clustered index in SQL Server 2008R2 Standard with a lot of unused space, as shown in the following image:

enter image description here

How do I reclaim unused space from MyTable?

Said table is declared as follows:

CREATE TABLE [dbo].[MyTable](
    [RecordID] [varchar](50) NULL,
    [DocumentID] [nvarchar](100) NULL,
    [DocumentName] [varchar](100) NULL,
    [DocumentOwner] [varchar](50) NULL,
    [DocumentTemplate] [varchar](50) NULL,
    [DocumentData] [ntext] NULL,
    [DocumentDate] [char](10) NULL,
    [DocumentTime] [char](10) NULL,
    [DocumentSize] [int] NULL,
    [DocumentUpdateVersion] [int] NULL,
    [SecondaryStorageURI] [varchar](150) NULL,
    [PreviousExportUri] [varchar](150) NULL

What steps should I follow to free unused space?

  • Are you looking for DBCC CLEANTABLE? If so, make sure to read the Best Practices paragraph on the documentation.
    – Rigerta
    Sep 21, 2017 at 15:46
  • 1
    Does your table have a clustered index? Sep 21, 2017 at 15:47
  • 1
    @RigertaDemiri that only works if you've dropped columns. Sep 21, 2017 at 15:48
  • @sp_BlitzErik, that is why I didn't post it as an answer :) And that's also what the "Best Practices" paragraph addresses. But there's not a lot of info on the question whether this is the case of not, you are right!
    – Rigerta
    Sep 21, 2017 at 15:50
  • @RigertaDemiri I wish it worked under more circumstances. Sep 21, 2017 at 15:52

2 Answers 2


If your table doesn't have a clustered index, then deletes don't deallocate empty pages by default.

Your options are:

  • ALTER TABLE dbo.MyTable REBUILD - which will take your table offline in Standard Edition, building a new copy of it with everything packed in nicely like sardines
  • Do your deletes with the TABLOCK hint - which can prove problematic for concurrency, since as it indicates, will take out a table lock to do the deletes
  • Truncate the table - which will deallocate all of the pages, not just the empty ones, so it has the unfortunate drawback of erasing all your data.
  • Put a clustered index on it - if you frequently update & delete your data, then you should do as Beyonce says: put a clustered index on it. Otherwise, you end up with the empty-space problem that you're having now, plus the forwarded-fetches problem.
  • "as Beyonce says" was something deleted? Who is Beyonce (besides the singer)?
    – jpmc26
    Sep 21, 2017 at 21:50
  • 2
    @jpmc26 "if you [frequently update and delete data from] it, then you shoulda put a [clustered index] on it" Sep 22, 2017 at 1:08

Given your table structure, the unused space is probably due to deletes of [DocumentData]. The fact that it's a heap isn't really important to this issue but it does simplify the solution a bit.

What I would do is rename the table, copy the data from the saved version into a new table and then drop the old one.

-- Step 1

sp_rename 'dbo.MyTable','dbo.MyTable_sav'

-- Step 2

SELECT [RecordID],
    [DocumentUpdateVersion] ,
    [SecondaryStorageURI] , 
INTO dbo.MyTable
FROM dbo.MyTable_sav

-- confirm steps 1 and 2

DROP TABLE dbo.MyTable_sav
  • 3
    Psst - you forgot about his nonclustered indexes.
    – Brent Ozar
    Sep 21, 2017 at 19:07
  • 2
    ALTER TABLE ... REBUILD would effectively accomplish the same thing, without having to shuffle things around manually. Extra steps to make are extra steps to mess up.
    – AMtwo
    Sep 26, 2017 at 15:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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