When you look at Microsoft’s documentation about restoring your BizTalk databases they always talk about restoring your databases by using log shipping. Most of my client have various reasons (unfamiliarity with the technique, requires extra servers and licenses, etc.) for not wanting to use log shipping. Alternatively you can manually restore the BizTalk databases.

My concern with manually restoring the databases is that it can take a lot of time and is prone to errors. By default the Backup BizTalk Server job creates a full backup once a day and a log backup every 15 minutes. Worst case scenario you will have to restore one full backup and (24 * 4) 96 logs times at least 5 databases. That means you will have to do 485 restore actions to restore the BizTalk databases to the latest state!

Today I want to talk to you about two small optimizations 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 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 database file
• And by 10% for log file

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