MDF Size Shrunk

  • Hi, need your expert opinions.

    The mdf file of our production database is originally sized 200 GB, after several tests on DB optimization, the mdf file was still around 200 GB, then after several days, we were surprised because it shrunk to 100 GB, and now it's back to its normal size of 200 GB.

    Our optimization plan included SHRINKFILE, SHRINKDATABASE, rebuilding Indexes using DBCC REINDEX. That's it. The commands performed as it was supposed to, like the log file shrunk, and the database backup also shrunk, but the mdf also shrunk to half its size and now its back to its normal size. How come...

    What could probably caused it? Pls I need your opinions, I'm afraid that we might lose some data because of it.

    Any opinion will be highly appreciated.

    Thanks,

    Isabel

  • Is your index optimization custom? Does it run for whole DB?

    Do not shrink your database/ log files. It will lead to fragmentation

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • You will not loose any data.

    Its just that your data file is of 200GB and there is no free space available.So no matter how much you shrink the files it will grow back to its original size.

    Use this for find the available space in database

    Use database_name

    sp_spaceused

    Tanx 😀

  • joeroshan (6/28/2009)


    Is your index optimization custom? Does it run for whole DB?

    Do not shrink your database/ log files. It will lead to fragmentation

    No, the index optimization does not run for the whole DB, it was only executed on selected tables.

  • Rebuild index often needs more free space than your biggest table. Rebuild task makes the database to grow. The space allocated is released, but not shrunk unless you do it manually. So in case if you normally first shrink the DB and then re index it, the database may always have the extra room.

    But shrinking data file is not advisable since it will lead to fragmentation. So do not change your plan. Try to avoid shrink totally if possible

    ----------------------------------------------------------------------------------------------------------------------------------------------------
    Roshan Joe

    Jeff Moden -Forum Etiquette: How to post data/code on a forum to get the best help[/url]

  • When you rebuild an index, SQL Server "populates" a new copy of the index, then deletes the old version once the new one has been created (this way there's no long rollback process if the server falls over in the middle of the rebuild). However, this means that there has to be enough free space in the database to hold two copies of the index.

    From what you've described I'm guessing that the growth factor of your .mdf file is 100GB (or maybe it's 50GB and it had to grow twice, or ...). You'd shrunk the file down to 100GB, but when the reindex ran it had to expand out again because it needed to have enough room to create the second copy of the index.

    It is very rare that you will need to shrink any database file. The only time I've ever shrunk a file is when we've archived off large quantities of data that will not be replaced. For example we have a database with 7 years' data but decided to make the online database hold only 2 years' data and move the earlier 5 years' data off to an archive database, with a monthly job to move the oldest month's data to the archive database. We were therefore able to shrink the database down to around 40% of the size it was previously. Out of a couple of thousand production databases I think I've done maybe a dozen shrinks in the last decade.

  • Why do you want to shrink? Databases tend to grow as more data gets put in them. It's in their nature.

    Shrinking causes massive fragmentation and will just result in the data file growing again next time data gets added. When that happens, the entire system will slow down as the file is expanded. Also repeated shrinks and grows will cause fragmentation at the file-system level, which is hard to fix.

    See - http://sqlinthewild.co.za/index.php/2007/09/08/shrinking-databases/

    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

Viewing 7 posts - 1 through 6 (of 6 total)

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