defrag a heap

• lotusnotes

  SSC Eights!

  Points: 833

  More actions

  March 3, 2005 at 4:21 am

  #163747

  Having run dbcc showcontig against a table which is a heap, how can you defrag it apart from physically moving it to another filegroup?

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  March 3, 2005 at 4:46 am

  #545154

  See if this helps:

  http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/ss2kidbp.mspx

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• AJ Ahrens

  SSC-Insane

  Points: 20676

  More actions

  March 3, 2005 at 5:39 am

  #545170

  Just for giggles......  Did you call the Garbage man to clean-up the trash heap?

  For those who don't get the reference (Oddly I get this a lot) "Trash heap" was the all-knowing wise one from Fraggle Rock and "Garbage man" refers to the all-knowing wise one in Dilbert. 

  
  
  Good Hunting!

  AJ Ahrens
  
  
  webmaster@kritter.net

• athurgar

  SSCrazy

  Points: 2816

  More actions

  March 4, 2005 at 3:21 am

  #545415

  Create a clustered index on the table and start moving in the rigt direction from a logical desigb perspective.

• mlipham

  Mr or Mrs. 500

  Points: 516

  More actions

  March 4, 2005 at 6:57 am

  #545447

  athurgar is right. Create a clustered index, then drop the index to return it to heap status.

  ------------------------------------

  create clustered index CookieMonster

  on

  (column)

  go

  drop index CookieMonster

  go

  ------------------------------------