Sql-server – How to reclaim the storage space used by a heap

sql serversql-server-2008-r2storage

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?

Best Answer

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.
Related Question