Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2003
    Posts
    223

    Unanswered: The performance regarding to AutoShrink

    Hello, everyone:

    If I select AutoShrink in database properties to release space to operating syetem, how does it make the perfomance to change. Thanks a lot.

    ZYT

  2. #2
    Join Date
    Aug 2004
    Posts
    54
    Autoshrink is usually not a good thing. Shrinking a database in general is not a good thing. In my opinion the only time a database should be shrunk is when the space is desperately needed. By turning autoshrink on you will be hurting performance. It will take processing power to shrink the database, then when more data is inserted it will take processing power to enlarge the database again. Personally I would never use autoshrink and I can’t think of any reason to use it.

    Have some fun.

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The performance will become worse. How much worse, depends on how many times you end up expanding the database, and freeing up space. Mostly, this will happen in the transaction log. The short of it is, writing to disk (and expanding/shrinking a file counts as a write to disk) is one of the longest operations in SQL Server.

    Suppose you have a batch process that uses up 500 MB of disk space in the transaction log. Suppose also that you have set the recovery to simple. Every night, the database will have to grow the transaction log back up to 500MB, if the database shrinks at any time during the day. THis will prolong the nightly batch job by however long it takes to format the space needed up to 500MB.

    Any clearer?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •