SQL Server Power Search - Get More Relevant Results

Autoshrink - Don't Do It!

The autoshrink database option is OK in a development environment, but avoid it like the plague on your production servers.

 

This option is often used in a development environment, due normally to lack of disk space, and when the database is released to the production environment the DBA omits to turn it off. Even worse, there are some DBAs who actually believe it is a good thing!

Automatic file shrinking is bad for 2 reasons:
  1. It is always used in conjunction with automatic file growth. The continuous grow-shrink-grow cycle can cause considerable fragmentation on the disk and may also result in internal fragmentation. This can adversely affect performance.
  2. You cannot control when the automatic shrinking will occur. It is performed at 30 minute intervals and can result in substantial I/O. An increase in I/O at times outside your control is obviously not a good idea.
So what is the answer?

Two words: "Capacity Planning". Ensure you have sufficient disk space for at least 2 year's growth of your data files, and also allow plenty of space for your log files. Even better than allocating disk space is to pre-size the files; this ensures that they occupy one contiguous chunk on the disk. If a log file regularly grows to 2GB (due maybe to re-indexing, or some other regular job) why shrink it back afterwards? You have to allow for the next time the space is needed so let it stay at that size.

My preference is to size my data and log files for future growth, but never use more than 80% of the available disk. This gives a bit of leeway should an emergency arise requiring use of the free space.

If you have a lot of databases and want to quickly tell if any have autoshrink on, query sys.databases.

Alternatively, download SQL CoPilot. This will highlight databases with autoshrink on, as well as a lot of other issues.