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.