5

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 Demiri Sep 21 '17 at 15:46
  • 1
    Does your table have a clustered index? – Erik Darling Sep 21 '17 at 15:47
  • 1
    @RigertaDemiri that only works if you've dropped columns. – Erik Darling Sep 21 '17 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 Demiri Sep 21 '17 at 15:50
  • @RigertaDemiri I wish it worked under more circumstances. – Erik Darling Sep 21 '17 at 15:52
14

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 '17 at 21:50
  • 2
    @jpmc26 "if you [frequently update and delete data from] it, then you shoulda put a [clustered index] on it" – Zac Faragher Sep 22 '17 at 1:08
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
  • 3
    Psst - you forgot about his nonclustered indexes. – Brent Ozar Sep 21 '17 at 19:07
  • 1
    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 '17 at 15:24

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.