Build Clustered Index to Free Space

Question

Build Clustered Index to Free Space

SwedishOrr

SSCrazy

Points: 2955
More actions
February 4, 2009 at 8:43 am

#130589

Hi All,
I recently used to sp_spaceused to assess the space usage of one of my tables. It turned out that it had a HUGE amount of unused space and didn't have a clustered index. In fact, there was no index at all. I did read somewhere (maybe on SSC) that building a clustered index would free up most of that unused space.
I built the clustered index and nearly ALL of the space was freed up, reducing my overall database size by 10 or so GB.
My question is WHY does building (or rebuilding) the clustered index have such a dramatic effect?
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 1

The results returned by sp_spaceused cannot be relied as perfect. Run DBCC updateusage (yourdatabase) with no_infomsgs,all_errormsgs and check what is the free space again.

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

SwedishOrr SSCrazy Points: 2955 More actions · Answer 2

Hi,

Thanks for that DBCC command. I just ran it successfully and reran the sp_spaceused SP and I see pretty much what I saw prior to running the DBCC command. I've got a third party piece of software that I use to check on space used by files and I know that after building the clustered index the unused space was dramatically decreased. I'm unclear on the details of why building a clustered index will free up most of that unused space?

Thanks!

The_SQL_DBA SSChampion Points: 10494 More actions · Answer 3

Your problems looks familiar to one of others I saw in this forum. Refer to this link, it might help you.

http://social.msdn.microsoft.com/Forums/en-US/sqldatabaseengine/thread/a58b33b7-3fd5-4fab-b805-074f5f1188d2/

Thanks!!

The_SQL_DBA
MCTS

"Quality is never an accident; it is always the result of high intention, sincere effort, intelligent direction and skillful execution; it represents the wise choice of many alternatives."

SwedishOrr SSCrazy Points: 2955 More actions · Answer 4

Hi,

Thanks for that link! Going through that information I understand now why building/rebuilding the clustered index can have such a big impact. I don't believe my issue is with forwarded records but I'm not 100% on that yet. I'll definitely keep reading and digging, but in the meantime I am pleased that I now understand a tad bit more of what's going on.

Thanks again!