Shrink database

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 7, 2013 at 8:43 am

  #1574358

  Terrie (1/7/2013)

  ---

  Thanks for all your replies. One shrinks a database to recover space. What does shrink with notruncate accomplish?

  Terrie

  Move pages to the beginning of the file. Mildly useful in certain situations. If not followed by rebuilding indexes, can result in a serious performance degredation.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  January 7, 2013 at 9:00 am

  #1574372

  Terrie (1/7/2013)

  ---

  Thanks for all your replies. One shrinks a database to recover space. What does shrink with notruncate accomplish?

  It does exactly the same as shrink does, just without releasing the empty space back to the file system.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Terrie

  Ten Centuries

  Points: 1353

  More actions

  January 7, 2013 at 9:15 am

  #1574381

  I'm just trying to make sure I understand. So shrink with notrucate moves the pages to the front of the file and does not release the space to the operating system. Do you stil risk fragmenting the disk?

• GSquared

  SSC Guru

  Points: 260824

  More actions

  January 7, 2013 at 9:17 am

  #1574384

  Terrie (1/7/2013)

  ---

  I'm just trying to make sure I understand. So shrink with notrucate moves the pages to the front of the file and does not release the space to the operating system. Do you stil risk fragmenting the disk?

  No. It doesn't do anything to the actual file structure as far as the disk is concerned. Just moves data around inside the file.

  The risk of physical level fragmentation is from repeatedly growing the file, not directly from shrinking it. Repeatedly shrinking is usually followed by repeatedly growing, so that's where that comes into play.

  - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
  Property of The Thread

  "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon