What’s so important about “Auto Growth”?
Firstly, let’s explore what it is and why it happens.
Auto-growth is an operation by which SQL Server expands the size of a database file (could be either the data file or the transaction log) when it runs out of space. How much depends on the settings that you have chosen; or if you’ve done no changes (which many don’t do), the default values of 1MB for data files and 10% for the log file. The growth settings available to choose for the files from are:-
- They can grow by a specific size
- Grow based on a percentage of the current size
- Not grow at all
- Unrestricted growth
- Set the growth of a database file to grow no larger than a specified size.
SQL Server, during the auto-growth operation, will block all current transactions until it completes the operation. If SQL Server is asking for a 1MB extension, that won’t take long. But imagine how long it will take if SQL Server was requesting for 2GB disk space?
On top of that, you need to understand that this disk space will not be contiguous (i.e., it won’t be physically right next to the existing database space), but instead will be somewhere else on the disk. You’re introducing disk fragmentation which will slow down SQL Server due to extra number of disk reads and writes that will now be necessary to support BizTalk. The more auto-growth operations you have, the more disk fragmentation!
That’s the theory, now let’s move onto what I reckon should be done and why.
1) Pre-size your BizTalk databases
You need to pre-size your databases so auto-growth is a contingency, not an expected behaviour.
Tim Form, a SQL Server MVP, believes that three years worth of transactions would be the basis of sizing your databases files. He even believes that auto growth is #10 of the Top 13 SQL Server Mistakes and Missteps
2) Pre-define the auto-growth setting for your data and transaction log files
Database auto-growth for both data and transaction log files should be set to 100MB instead of 1MB and 10% (respectively).
So, putting these together is very well summed up in the Microsoft BizTalk Performance Optimisation Guide. The guide states:
- BizTalk DTADB (BizTalk Tracking database files): Data file having a file size of 2048 MB with 100 MB growth and a log file of 1024 MB with 100 MB growth.
- BizTalkMgmtdb (BizTalk Management database files): 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: 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 databases): 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.
Here’s a screenshot of the database properties of the BizTalkDTADb with the guidelines in place.
Until next tip… be good!