SQL Server: Should I have auto-shrink turned on?

1142720 pts.
Tags:
SQL Server
SQL Server database
This is a basic SQL Server question, but for my database, should I use the auto-shrink option. But someone told me it's not really secure. Is that true?
1

Answer Wiki

Thanks. We'll let you know when a new response is added.

No you shouldn’t turn it on due to the following reasons – 

  1. Shrink-grow-shrink-grow causes file-system level fragmentation and takes lots of resources.
  2. You can’t control when it kicks-in (even though it’s regular-ish)
  3. It uses lots of resources. Moving pages around in the database takes CPU, lots of IO, and generates lots of transaction log.
  4. Here’s the real kicker: data file shrink (whether auto- or not) causes massive index fragmentation, which leads to poor performance.

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • ToddN2000
    I personally do not use it for the performance reasons listed above.  A lot of my databases are fairly small as they are for web service interfaces to the corporate DB2 system. I only need some validation data tables and not a permanent database that grows daily.
    132,690 pointsBadges:
    report
  • danieljones
    Auto shrink makes server terribly slow, because all it did all day was shrink and grow the database files.
    2,840 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: