How to check your SQL Server Database Autogrowth

Autogrowth is a great thing on Databases, it allows you to set how big a file can get and how it growth to get there. This control is one feature that very few DBAs I know actually utilise.

For most of us this is just a “oh yes, that thing” type of feature and we do not look at the impact this has on your database logging.

As always there is a lovely article from Microsoft http://support.microsoft.com/kb/315512 that explains all of this very nicely and why it is a good idea to do it right.

Here is the summary of best practices.

Best Practices

  1. For a managed production system, you must consider autogrow to be merely a contingency for unexpected growth. Do not manage your data and log growth on a day-to-day basis with autogrow.

  2. You can use alerts or monitoring programs to monitor file sizes and grow files proactively. This helps you avoid fragmentation and permits you to shift these maintenance activities to non-peak hours.

  3. AutoShrink and autogrow must be carefully evaluated by a trained Database Administrator (DBA); they must not be left unmanaged.

  4. Your autogrow increment must be large enough to avoid the performance penalties listed in the previous section. The exact value to use in your configuration setting and the choice between a percentage growth and a specific MB size growth depends on many factors in your environment. A general rule of thumb to you can use for testing is to set your autogrow setting to about one-eight the size of the file.

  5. Turn on the <MAXSIZE> setting for each file to prevent any one file from growing to a point where it uses up all available disk space.

  6. Keep the size of your transactions as small as possible to prevent unplanned file growth.

This is all well and good, but wouldn’t it be nice to have some simple code to give you a quick look at your database?

exec sp_MSforeachdb @command1 = '