While maintaining and fine-tuning a BizTalk environment this week, I found out, alongside other non-compliances, that the BizTalk MessageBox database size was 3.5GB, which at first glance is not critical because most of the time the file size grows due to the volume of messages processed – and you have to realize that the BizTalk Server database files will increase automatically if there is that need but they will never automatically decrease. For that, you need to have a maintenance process to stop the environment and release the allocated space by the files (shrink task)
However, while trying to release the space allocated to the data and transaction files, I realized that the transaction file had been successfully shrunk, but the data file was stuck at 3.5GB. And when I checked on the SQL Server Management Console, I found that there was no free space to be released, which meant that there really was this amount of data in the MessageBox. This was actually a critical problem since nothing was being processed by the environment. All processes had been completed successfully, and there were no suspended messages.
Cause
To analyze the MessageBox database better and to find out where this amount of data was present, I ended up using this simple query to check the number of rows for each table in the database. This query works for all databases:
CREATE TABLE #counts
(
table_name varchar(255),
row_count int
)
EXEC sp_MSForEachTable @command1='INSERT #counts (table_name, row_count) SELECT ''?'', COUNT(*) FROM ?'
SELECT table_name, row_count FROM #counts ORDER BY table_name, row_count DESC
DROP TABLE #counts
I was surprised to see that almost 1 million lines were on the tracking tables, waiting to be moved to the DTA database.
Even more surprising was to learn that the Tracking Host Instance was working, which could only be related to the performance of the tracking database and the job that moves the information.
Solution
Of course, the basic answer is to guarantee that the Tracking Host Instance and the TrackedMessages_Copy job are running and waiting for the data to be moved. But we wanted to solve this quickly and have our environment in good shape again, so we decided to discard all of this tracking data that was not essential to us. To do that, you need to:
- 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 TrackingData Tables in MsgBox.
- Click Execute Task.
And then finally:
- Shrink the MessageBox database by running the following script:
USE BizTalkMsgBoxDb
GO
ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (BizTalkMsgBoxDb)
GO
DBCC SHRINKFILE (BizTalkMsgBoxDb_Log)
GO
ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO
- Pre-allocate space to the MessageBox database by running the following script:
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
- And guarantee that the Tracking Host Instance and the TrackedMessages_Copy job are running correctly in the end.
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!
