Saturday, June 13, 2009

DBCC SHRINKDATABASE Will it cause Index Fragmentation Series 4 (Explanation)

Well as I learnt in previous blog Shrink Database caused fragmentation, but why? Before we discuss fragmentation, let me try to explain DBCC ShrinkDatabase command.

DBCC ShrinkDatabase command takes database name, Target Percent and Truncate | NoTruncate as parameters.

Truncate or NoTruncate indicate to database engine if space reclaimed by database engine be given back to Operating System or not.

Truncate: SQL Database engine releases space to OS, NOTruncate: Does not release space to OS though file content is  (leaving empty space at the end).

Target Percent is slightly confused here. Target Percent is not Target Size that database should be post shrinking operation but Target Percent is Target free space database should have post shrinking.

When database is shrunk either by Management Studio or DBCC command it moves data from end of file to beginning or starting of file. It tries to fill pages as it gets to move from end to start of file. So pages can not be moved in chunks (as there may not be huge chunks of pages at start in a normal production environment) but pages maybe moved individually inducing fragmentation.

In this case I have learnt that a completely defragmented index is fragmented to 97% post shrink operation and that is terrifying with regards to performance. Are you all seeing the same :)

Thus if one if forced to do a shrink operation it should be followed by a rebuilding Index (DBCC DBREINDEX) to defragment index.

Also this is one of reasons for Auto Shrink Database Property to be turned off.

Now in a later post I would like to check if Compressing and Decompressing an existing table will induce fragmentation or not or maybe something more interesting :)

If you have any questions / comments / corrections pertaining to any of fragmentation details please feel free to send mail to me at gurucb@hotmail.com

7 comments:

Anonymous said...

Inhibit Our Risqu‚ Prices at www.Pharmashack.com, The Hose [b][url=http://www.pharmashack.com]Online Pharmaceutical chemist's [/url][/b] To [url=http://www.pharmashack.com]Buy Viagra[/url] Online ! You Can also Unearth Countless Deals When You [url=http://www.pharmashack.com/en/item/cialis.html]Buy Cialis[/url] and When You You [url=http://www.pharmashack.com/en/item/levitra.html]Buy Levitra[/url] Online. We Also Careen a Aristocrats crust Generic [url=http://www.pharmashack.com/en/item/phentermine.html]Phentermine[/url] In holdings of Your Comestibles ! We Purloin up M‚row attend to [url=http://www.pharmashack.com/en/item/viagra.html]Viagra[/url] and Also [url=http://www.pharmashack.com/en/item/generic_viagra.html]Generic Viagra[/url] !

Anonymous said...

Make the animalistic with two backs casinos? separate this advanced [url=http://www.realcazinoz.com]casino[/url] superintend and uphold a combat up online casino games like slots, blackjack, roulette, baccarat and more at www.realcazinoz.com .
you can also dilly-dallying our good palate [url=http://freecasinogames2010.webs.com]casino[/url] evade at http://freecasinogames2010.webs.com and be winning in open compressed create of !
another girder [url=http://www.ttittancasino.com]casino spiele[/url] idea is www.ttittancasino.com , in compensation german gamblers, collect well-wishing online casino bonus.

Anonymous said...

I read this forum since 2 weeks and now i have decided to register to share with you my ideas. [url=http://inglourious-seo.com]:)[/url]

Anonymous said...

Making money on the internet is easy in the underground world of [URL=http://www.www.blackhatmoneymaker.com]blackhat code[/URL], It's not a big surprise if you haven’t heard of it before. Blackhat marketing uses not-so-popular or little-understood methods to generate an income online.

Anonymous said...

las vegas pharmacy jobs http://drugstore4.com/product/feldene.html define pharmacy [url=http://drugstore4.com/product/epivir.html]epivir[/url]
marie gardner pharmacy faculty http://drugstore4.com/product/himalaya-pilex-ointment.html pharmacy belize [url=http://drugstore4.com/category/anticonvulsant.html]canadian pharmacy school[/url]
university of tennessee college of pharmacy http://drugstore4.com/fr/product/altace.html ranch pharmacy scottsdale [url=http://drugstore4.com/product/epivir.html]epivir[/url]
discount levitra pharmacy purchase http://drugstore4.com/category/herbals.html?page=1 unc pharmacy [url=http://drugstore4.com/fr/product/aceon.html]ethics in pharmacy[/url]

Anonymous said...

california highway travel http://foxusa.eu/cruise/royal-caribean-international-cruise-line value travel destinations [url=http://qipp.eu/flight/flight-trousers]flight trousers[/url]
monogram travel suticase http://foxusa.eu/cruise/triumph-carnival-cruise travel between victoria station and cambridge [url=http://foxusa.eu/tours/maverick-tours-and-lasvegas]essential travel guide[/url]
erxperiments of orbital travel amd time http://qipp.eu/tours/savannah-georgia-walking-tours wisconsin travel distance [url=http://qipp.eu/car-rental/rental-car-france]rental car france[/url]
have camera will travel tv show 1956 http://qipp.eu/adventure/fancy-pants-adventure-2-black-pants bangkok travel agent flights flight london cheap [url=http://qipp.eu/cruises]philippine travel tour exposition[/url]
air travel to david panama http://foxusa.eu/motel/motel-marshall-il travel space to molecule [url=http://qipp.eu/tour/tour-catering-corporate]tour catering corporate[/url]
nam ho travel singapore http://qipp.eu/adventure/dog-sled-adventure restored vintage travel trailers [url=http://qipp.eu/car-rental/santa-barbara-car-rental]caa travel south west ontario[/url]

Anonymous said...

good sams travel clubs http://jtfb.info/shemale/salma-shemale salma shemale [url=http://jtfb.info/pissing/glamorous-ladies-stockingtops-garters-pissing]glamorous ladies stockingtops garters pissing[/url]
http://jtfb.info/nylon/tytan-international-twisted-nylon-twine tytan international twisted nylon twine don moen music [url=http://jtfb.info/pissing/grannies-pissing-pics]grannies pissing pics[/url]
[url=http://jtfb.info/thong/worlds-most-confortable-thong]worlds most confortable thong[/url] anne hathways new movie http://jtfb.info/tit/big-tit-exhibisionist big tit exhibisionist
travel tibet http://jtfb.info/nylon/uncle-mikes-nylon-shot-gun-slings uncle mikes nylon shot gun slings [url=http://jtfb.info/tits/mileys-tits]mileys tits[/url]
http://jtfb.info/prostitute/charlize-theron-prostitute-scenes-online charlize theron prostitute scenes online new orleans jazz funeral music [url=http://jtfb.info/defloration/littel-defloration]littel defloration[/url]