Partilhar via

DB Autoshrink option may introduce high fragmentation

  • Artigo

Issue

We have seeing some strange behavior when rebuilding indexes.

· Total Fragmentation prior to rebuild is 99%.

· You rebuild the index. (using both the alter index and the dbcc dbreindex)

· Fragmentation is at 0%

· You recheck fragmentation about a minute later.

· Fragmentation is at 99% again.

· You have verified that there are no DML's being done on that table.

Analysis

We noticed that the database has “Auto Shrink” enabled.

Status

After we disabled the auto shrink, it’s working fine.

Comments

  • Anonymous
    January 10, 2008
    The comment has been removed

  • Anonymous
    January 10, 2008
    This has been a well known issue for many years - I even had Books Online updated for 2005 when I was at MS to advise against auto-shrink and shrink in general. See http://www.sqlskills.com/blogs/paul/2007/11/13/AutoshrinkTurnItOFF.aspx for a detailed explanation of this along with a script that shows shrink causing total fragmentation in an index. Thanks

  • Anonymous
    January 11, 2008
    It's common knowledge that shrinking a database leads to internal fragmentation.

  • Anonymous
    April 11, 2010
    thank you great article. Save my files. http://www.erotikvideoizle.net