ALTER DATABASE REMOVE AUTOSHRINK PERMANENTLY

When a database gets created, restored, or attached to SQL Server, this T-SQL command should execute by default. Seriously, if this command exists, that’s probably how most database administrators will find it applicable. The side effects of auto-shrink outweigh any potential benefits. Below are some of the disadvantages of auto-shrink.

  • It will lead to indexes becoming fragmented
  • Auto-shrink has a mind of its own. You will not have control over when it runs
  • It uses IO and CPU, thus taxing the system of essential resources
  • It will move data through the buffer, flushing pages in memory to disk, and cause a performance decrease

I am not the only one against auto-shrink. SQL Server gurus such as Brent Ozar, Paul Randall, Klaus Aschenbrenner and much more are also part of the movement. Auto-shrink is still widely used as part of a database maintenance plan or occasionally to cause chaos. Regardless of the environment, DBAs are still using auto-shrink as a way of reclaiming disk space, ignoring the well-documented disadvantages of using it.

If you need to release free space back to disk, use DBCC SHRINKFILE(<logical file name>, <target size>, TRUNCATEONLY). Indexes will not become fragmented when you use the TRUNCATEONLY option. Furthermore, if you don’t want to specify a target size, pass the number 0 and the file will be truncated to the last allocated extent.

Leave A Comment

Please be polite. We appreciate that. Your email address will not be published and required fields are marked