- SQL Server database auto-growth is a blocking operation, which hinders BizTalk Server database performance. Therefore it is important to allocate sufficient space for the BizTalk Server databases in advance to minimize the occurrence of database auto-growth.
- Database auto-growth should be set to a fixed number of megabytes instead of to a percentage (specify file growth In Megabytes). This should be done so that, if auto-growth occurs, it does so in a measured fashion. This reduces the likelihood of excessive database growth. The growth increment for BizTalk Server databases should generally be no lower than 100 MB.
- 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 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=132063) in the SQL Server books online. This topic provides steps for enabling instant file initialization.
To check the settings I can off course manually look at every BizTalk database, but this is just way too much work :).
Running the following query will give you a much faster overview of the database autogrowth settings:
exec sp_MSforeachdb @command1 = 'use [?]; EXEC sp_helpfile'