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

Posted: September 18, 2012 in BizTalk
Tags: , , ,

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

Autogrowth

There’s a great post from Jeroen Hendriks: Checking database autogrowth 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.

Autogrowth-BizTalkMgmtDb
Autogrowth-BizTalkMsgBoxDb

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:

  1. 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.
  2. 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 by two ways:

  • Manually, by opening “Microsoft SQL Server Management Studio”
    • Maximize you SQL Server and then Databases
    • Right-click in 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 to database and log file.

Database-properties

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 =  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 =  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 =  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 =  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 =  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 =  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 having 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 having 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 having 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 having 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 having 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 having 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 environment you should consider devide the BizTalkMsgBoxDb in 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: http://msdn.microsoft.com/en-us/library/ee377048.aspx

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    (NAME = BizTalkMsgBoxDb_2 , FILENAME = 'J:\BizTalkMsgBoxDb_2.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_3 , FILENAME = 'J:\BizTalkMsgBoxDb_3.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_4 , FILENAME = 'J:\BizTalkMsgBoxDb_4.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_5 , FILENAME = 'J:\BizTalkMsgBoxDb_5.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_6 , FILENAME = 'J:\BizTalkMsgBoxDb_6.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (NAME = BizTalkMsgBoxDb_7 , FILENAME = 'J:\BizTalkMsgBoxDb_7.ndf' , SIZE = 2GB , FILEGROWTH = 100MB)
ALTER DATABASE BizTalkMsgBoxDb ADD FILE    (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 =  20GB , FILEGROWTH = 100MB)
GO

The script can be found and download on Microsoft TechNet Gallery:
Pre-allocate space and define auto-growth settings for BizTalk Server databases (1.5 KB)
Microsoft TechNet Gallery

About these ads
Comments
  1. 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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s