Post reply

Effects of shrink the database in SQL Server 2005

  • October 7, 2010 at 8:38 am

    #148927

    Effects of shrink the database in SQL Server 2005 of size 97GB in PROD.

    My question? Is it a good sign of shrinking the database weekly one. as running out of space. Are there any side effects like decreasing the performance.

    Thanks in advance.

  • October 7, 2010 at 8:44 am

    #1233646

    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

  • October 7, 2010 at 8:51 am

    #1233651

    Gail is correct. You don't want to do this, and the database will grow as you add data. Would you expect anything else?

    You want to manage space. You need a certain amount of free space in the database to accommodate maintenance and data growth. If you shrink, you will fragment indexes.

    If you are running out of disk space, buy more. Or remove data.

Viewing 3 posts - 1 through 2 (of 2 total)

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