6

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?

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
DAVID
  • 71
  • 1
  • 5

2 Answers2

17

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.
Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
2

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],
    [DocumentID],
    [DocumentName],
    [DocumentOwner],
    [DocumentTemplate],
    [DocumentData],
    [DocumentDate],
    [DocumentTime],
    [DocumentSize],
    [DocumentUpdateVersion] ,
    [SecondaryStorageURI] , 
    [PreviousExportUri]  
INTO dbo.MyTable
FROM dbo.MyTable_sav

-- confirm steps 1 and 2

DROP TABLE dbo.MyTable_sav
M. Mullane
  • 29
  • 3