Today, while maintaining and fine-tuning a BizTalk environment, I found several non-critical and some critical warnings on the BizTalk Health Monitor. One of these critical warnings was related to database sizes: Large size on DTA db can impact also MsgBox Db performance, check Tables size and if ‘DTA Purge and Archive’ job is running.
DTA database size (MB)
Total DB Space Used for DTA DB “BizTalkDTADb” on <server-name>
32152.37 (Large size on DTA db can impact also MsgBox Db performance, check Tables size and if ‘DTA Purge and Archive’ job is running)
Cause
There are many reasons why the DTA database has started to grow, such as:
- BizTalk SQL Server Agent jobs are not running;
- Disk failures
- Too much tracking data
- Tracking data retention time
- SQL Server performance
- Network latency
- SQL Server performance
- Network latency
In our case, it was a mix of several reasons.
Important tip: Large BizTalk Server databases greatly impact the environment’s performance!
Solution
There are many ways to solve this issue, but when something starts to be in a critical mode, the best procedure to solve this will be:
- Stop the BizTalk Server environment – THIS IS MANDATORY! – That means stop the following components:
- Stop all BizTalk Services: Host Instances, Enterprise Single Sign-On, and so on…
- Disable all BizTalk SQL jobs or Stop the SQL Server Agent Service.
- Purge all tracking data from the DTA database by:
- Access the BizTalk Health Monitor tool and select Maintenance.
- Select the default profile and make sure you select all options in order to Connect.
- On the Task type combo box, select the option Delete. And on the Task list combo box, select the option PURGE Everything in the DTA.
- Click Execute Task.
- Shrink the DTA database by running the following script:
USE BizTalkDTADb
GO
ALTER DATABASE BizTalkDTADb
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (BizTalkDTADb)
GO
DBCC SHRINKFILE (BizTalkDTADb_Log)
GO
ALTER DATABASE BizTalkDTADb
SET RECOVERY FULL
GO
- Pre-allocate space to the DTA database by running the following 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
- Optional:
- Make sure you set the correct retention time of the DTA tracking data on the DTA Purge and Archive (BizTalkDTADb) job
- Make sure you set a proper tracking policy for the BizTalk Server artifacts. Avoid shape start and end tracking in orchestration and avoid body tracking in all ports and with all options.
- Start your BizTalk Server environment by:
- Enable all BizTalk SQL jobs or Start the SQL Server Agent Service.
- Start all BizTalk Services: Host Instances, Enterprise Single Sign-On, and so on…
I hope you find this helpful! If you liked the content or found it useful and want to help me write more, you can buy (or help me buy) my son a Star Wars Lego!
Thanks. Great tips! BHM thresholds can be tuned also in case you want to set higher or lower threshold. It can vary quite a lot.
Also something many customers miss when moving from fast on-prem SAN to Azure disks, is that they need similar speed. Unfortunately I see customers choosing Standard HDD low speed disks! I recommend at least Premium SSD P10/P20 type for OS and data disks (remember OS disk is usually busy as well and MSDTC storage is residing on OS disk), and most customers need multiple disk. The new Azure Premium v2 SSD disk type has more tuning if performance is needed, e g some customers have been very successful and happy last years during Black Friday huge load by adjusting Premium v2 SSD disks.
I usually recommend to pre-grow to 150% of estimated max size, but also increase the growth to avoid VLFs in the database, in case there are still future growths.
After a purge or other big change or on regular interval I also recommend to do reindex of DTADb (and MsgBoxDb).
Most customers I see who use DTA usually need at least 5GB, so can be good to pre-grow a bit more, e g :
——
ALTER DATABASE BizTalkDTADb MODIFY FILE (NAME = BizTalkDTADb , SIZE = 5GB , FILEGROWTH = 1000MB)
GO
ALTER DATABASE BizTalkDTADb MODIFY FILE (NAME = BizTalkDTADb_log , SIZE = 5GB , FILEGROWTH = 1000MB)
GO
——
Rebuild indexes / reindex:
https://learn.microsoft.com/en-us/troubleshoot/biztalk/maintain-troubleshoot-database
https://learn.microsoft.com/en-us/troubleshoot/biztalk/biztalkmsgboxdb-connection-issue
BizTalkMsgBoxDb : bts_RebuildIndexes stored procedure
BizTalkDTADb : dtasp_RebuildIndexes stored procedure
–Stop Host instances, IIS and SQL Agent service
Use BizTalkMsgBoxDB
exec bts_RebuildIndexes
USE BizTalkDTADb
exec dtasp_RebuildIndexes
–Restart services
Reindex other databases (MgmtDb, SSODB, BAM etc) :
—–
USE [DATABASE_NAME]
EXEC sp_MSforeachtable @command1=”print ‘?'”, @command2=”SET QUOTED_IDENTIFIER ON; ALTER INDEX ALL ON ? REBUILD WITH (ONLINE=OFF)”
—–