Posted by Erland Sommarskog on 01/01/08 09:56

David Greenberg (davidgr@iba.org.il) writes:
> So when does SqlServer decide to shrink the database automaticaly ? When
> a backup is performed ?

To have SQL Server automatically shrink a database, you have configure
the database to be in auto-shrink mode, which I strongly recommend against.
As I said, shrinking is something you should do only exceptionally, and
when you do, you want to have control over it.

If you are in AUTO_SHRINK mode, Books Online tells us this:

The database files are candidates for periodic shrinking.

Both data file and log files can be automatically shrunk. AUTO_SHRINK
reduces the size of the transaction log only if the database is set to
SIMPLE recovery model or if the log is backed up. When set to OFF, the
database files are not automatically shrunk during periodic checks for
unused space.

The AUTO_SHRINK option causes files to be shrunk when more than 25
percent of the file contains unused space. The file is shrunk to a size
where 25 percent of the file is unused space, or to the size of the
file when it was created, whichever is larger.

You cannot shrink a read-only database


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]