Why you should NOT shrink an MSSQL database

  • You can shrink MSSQL databases with the command: dbcc shrinkdatabase (xxx).
  • See: DBCC SHRINKDATABASE with MSSQL
  • dbcc shrinkdatabase (xxx) will reduce the size of the database by moving the data in the file from the end of a file to the unallocated pages at the front of the file. This is fine as long as there is no index involved.
  • If the AUTO_SHRINK turned on, the dbcc shrinkdatabase process will start up every 30 minutes and try to shrink each database that allows for the AUTO_SHRINK.
  • If the database is a read/write operation it will grow in size when adding new data, and will try to shrink again 1/2 hour later.
  • Repeatedly shrinking and growing data files cause file-system level fragmentation.
  • By default the databases created on SQL Server 2005 and SQL Server 2008, the AUTO_SHRINK option is off.

Applies to:

  • Microsoft SQL Server 2000
  • Microsoft SQL Server 2005
  • Microsoft SQL Server 2008