Export (0) Print
Expand All

Defining Auto-Growth Settings for Databases

You should set database auto-growth to a fixed number of megabytes instead of to a percentage, especially for the MessageBox and BizTalk Tracking databases. Depending on your BizTalk application and throughput, the MessageBox and Tracking databases can get quite large. If you set auto-growth to a percentage, then the auto-growth can be substantial as well.

When SQL Server increases the size of a file, it must first initialize the new space before it can be used. This is a blocking operation that involves filling the new space with empty pages. SQL Server 2005 or later running on Windows Server 2003 or later supports “instant file initialization.” This can greatly reduce the performance impact of a file growth operation. For more information, see "Database File Initialization" (http://go.microsoft.com/fwlink/?LinkId=101579) in the SQL Server documentation. This topic outlines the steps that must be taken to enable instant file initialization.

For steps on enabling instant file initialization, see "Database File Initialization" (http://go.microsoft.com/fwlink/?LinkId=101579) in the SQL Server documentation. For creating file groups and moving the BizTalk Server database tables, indexes, and log files into the appropriate file groups, follow the recommendations in "Appendix B - Recommended BizTalk Server Database Configuration" in the "BizTalk Server Database Optimization" white paper (http://go.microsoft.com/fwlink/?LinkID=101578). Ideally the size of files supporting the file groups should be pre-allocated and if possible, set to a static size.

If the system is new and the static sizes have not been definitively established, then configure files with the Enable Autogrowth option and specify file growth In Megabytes. The growth increment should generally be no larger than 100 MB (for large files), 10 MB (for medium-sized files), or 1 MB (for small files).

Show:
© 2014 Microsoft