Pre-allocate space and define auto-growth settings for BizTalk Server databases

Today I want to talk to you about two small optimizations that are often forgotten but with performance implications. Some of these implications may be noticed more in the early days of our BizTalk environment when the databases are small or almost empty and often need to increase, others can have an impact later when databases are large and the percentage increase is also too large.

By default BizTalk Server databases are defined with small files size and with the parameter “Autogrowth” set to:

Increase by 1 MB for the database file
And by 10% for log file

There’s a great post from Jeroen Hendriks: Checking database auto-growth settings where it is explained how we can check all BizTalk Server database settings using an SQL query and also Database file size – data collection so that we can recommend the right values.

So what’s the problem with these settings?

Auto growth setting plays an important role in BizTalk configuration for performance reasons, why?

SQL Server database auto-growth is a blocking operation which hinders BizTalk Server database performance. 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.

Therefore it’s recommended to:

Set this value (databases auto-growth) to a fixed value of megabytes instead of to a percentage, so SQL server doesn’t waste is resources expanding the data and log files during heavy processing. This is especially true for the MessageBox and Tracking (DTA) databases:
1. In a high throughput BizTalk Server environment, the MessageBox and Tracking databases can significantly increase. If auto-growth is set to a percentage, then auto-growth will be substantial as well.
2. As a guideline for auto-growth, for large files increment should be no larger than 100 MB, for medium-sized files 10 MB, or for small files 1 MB.
3. 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.
Also, allocate sufficient space for the BizTalk Server databases in advance to minimize the occurrence of database auto-growth.

How can I implement these optimizations?

You can do these optimizations in two ways:

Manually, by opening “Microsoft SQL Server Management Studio”
- Maximize your SQL Server and then Databases
- Right-click on the database, for example, “BizTalkMsgBoxDb” and select “Properties” option
- On Database Properties window, select Files page option and then you can check and change Autogrowth property associated with database and log file.

However, checking and changing all BizTalk databases manually is too much work and can be a little boring …

Or you can do this by running the following SQL Script:

ALTER DATABASE BizTalkDTADb MODIFY FILE (NAME = BizTalkDTADb , SIZE = 2GB , FILEGROWTH = 100MB)
GO
ALTER DATABASE BizTalkDTADb MODIFY FILE (NAME = BizTalkDTADb_log , SIZE =&amp;nbsp; 1GB , FILEGROWTH = 100MB)
GO

ALTER DATABASE BizTalkMgmtdb MODIFY FILE (NAME = BizTalkMgmtDb , SIZE = 512MB , FILEGROWTH = 100MB)
GO
ALTER DATABASE BizTalkMgmtdb MODIFY FILE (NAME = BizTalkMgmtDb_log , SIZE =&amp;nbsp; 512MB , FILEGROWTH = 100MB)
GO

ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb , SIZE = 2GB , FILEGROWTH = 100MB)
GO
ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb_log , SIZE =&amp;nbsp; 2GB , FILEGROWTH = 100MB)
GO

ALTER DATABASE SSODB MODIFY FILE (NAME = SSODB , SIZE = 512MB , FILEGROWTH = 100MB)
GO
ALTER DATABASE SSODB MODIFY FILE (NAME = SSODB_log , SIZE =&amp;nbsp; 512MB , FILEGROWTH = 100MB)
GO

ALTER DATABASE BAMPrimaryImport MODIFY FILE (NAME = BAMPrimaryImport , SIZE = 150MB , FILEGROWTH = 10MB)
GO
ALTER DATABASE BAMPrimaryImport MODIFY FILE (NAME = BAMPrimaryImport_log , SIZE =&amp;nbsp; 150MB , FILEGROWTH = 10MB)
GO

ALTER DATABASE BAMArchive MODIFY FILE (NAME = BAMArchive , SIZE = 70MB , FILEGROWTH = 10MB)
GO
ALTER DATABASE BAMArchive MODIFY FILE (NAME = BAMArchive_log , SIZE =&amp;nbsp; 200MB , FILEGROWTH = 10MB)
GO

ALTER DATABASE BizTalkRuleEngineDb MODIFY FILE ( NAME = BizTalkRuleEngineDb , FILEGROWTH = 1024KB )
GO
ALTER DATABASE BizTalkRuleEngineDb MODIFY FILE ( NAME = BizTalkRuleEngineDb_log , FILEGROWTH = 1024KB )
GO

The execution of this SQL script will set automatically the values for all BizTalk Server databases according to what is recommended. Not only the auto-growth property but also the database and log file size:

BizTalkDTADb (BizTalk Tracking database): Data file has a file size of 2 GB with 100 MB growth and a log file of 1 GB with 100 MB growth.
BizTalkMgmtdb (BizTalk Management database): Data file has a file size of 512 MB with 100 MB growth and a log file of 512 MB with 100 MB growth.
SSODB (SSO database): Data file has a file size of 512 MB with 100 MB growth and a log file of 512 MB with 100 MB growth.
BizTalkMsgBoxDb (BizTalk MessageBox database): Data file has a file size of 2 GB with 100 MB growth and a log file of 2 GB with 100 MB growth.
BAMPrimaryImport (BAM Primary Import database): Data file has a file size of 150 MB with 10 MB growth and a log file of 150 MB with 100 MB growth.
BAMArchive (BAM Archive): Data file has a file size of 70 MB with 10 MB growth and a log file of 200 MB with 10 MB growth.
BizTalkRuleEngineDb (Rule Engine database): Data file with 1 MB growth and a log file with 1 MB growth.

Note: These values were used for a standalone environment. In a high throughput BizTalk Server environments, you should consider dividing the BizTalkMsgBoxDb into 8 data files, each having a file size of 2 GB with 100 MB growth and a log file of 20 GB with 100 MB growth. Because the BizTalk MessageBox databases are the most active, we recommend you place the data files and transaction log files on dedicated drives to reduce the likelihood of problems with disk I/O contention, as is explained here: Post-Configuration Database Optimizations

EXEC dbo.sp_helpdb BizTalkMsgBoxDb
ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb , FILENAME = 'J:\BizTalkMsgBoxDb.mdf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE&amp;nbsp;&amp;nbsp;&amp;nbsp; (NAME = BizTalkMsgBoxDb_2 , FILENAME = 'J:\BizTalkMsgBoxDb_2.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE&amp;nbsp;&amp;nbsp;&amp;nbsp; (NAME = BizTalkMsgBoxDb_3 , FILENAME = 'J:\BizTalkMsgBoxDb_3.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE&amp;nbsp;&amp;nbsp;&amp;nbsp; (NAME = BizTalkMsgBoxDb_4 , FILENAME = 'J:\BizTalkMsgBoxDb_4.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE&amp;nbsp;&amp;nbsp;&amp;nbsp; (NAME = BizTalkMsgBoxDb_5 , FILENAME = 'J:\BizTalkMsgBoxDb_5.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE&amp;nbsp;&amp;nbsp;&amp;nbsp; (NAME = BizTalkMsgBoxDb_6 , FILENAME = 'J:\BizTalkMsgBoxDb_6.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE&amp;nbsp;&amp;nbsp;&amp;nbsp; (NAME = BizTalkMsgBoxDb_7 , FILENAME = 'J:\BizTalkMsgBoxDb_7.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE&amp;nbsp;&amp;nbsp;&amp;nbsp; (NAME = BizTalkMsgBoxDb_8 , FILENAME = 'J:\BizTalkMsgBoxDb_8.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
GO
ALTER DATABASE BizTalkMsgBoxDb MODIFY FILE (NAME = BizTalkMsgBoxDb_log , FILENAME = 'K:\BizTalkMsgBoxDb_log.LDF', SIZE =&amp;nbsp; 20GB , FILEGROWTH = 100MB)
GO

Download

You can download the script here:
Pre-allocate space and define auto-growth settings for BizTalk Server databases
GitHub

Author: Sandro Pereira

Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira

1 thought on “Pre-allocate space and define auto-growth settings for BizTalk Server databases”

Niko Neugebauer says:

September 18, 2012 at 3:04 pm

Sandro, you have got to be careful with VLFs (Virtual Log Files) when changing and setting autogrowth values.
VLF is a small and almost invisible thing, but wrong setting cause severe performance issues and unreasonable recovery times (think restart).

In the case of a datafile, you might be able to configure “Instant Growth” on your Windows Server, but in the case of the log files every time they grow – everything stops.

That is why it is common to say that the best practice is to set the values high enough, so that there won’t be a need for any growth, but not to high in order to waste space.

In any cases those values should be carefully monitored.