Therefore following little guideline that you can use as a precaution, as well as a fix while dealing with a huge DTA database.
1. Make sure you have enough disk space
The DTA database can grow quite large from one moment to another, so its best to take into account a quite large disk where the database will be stored. A DTA database stays, according to Microsoft guidelines, healthy in size until 15GB. Everything above 15GB is considered problematic and needs to be dealt with.
When adding the numbers in terms of size of the DTA db together with the other BizTalk databases, make sure you have around 30GB of disk space allocated for the databases itself. When storing the backups of the databases also on the same disk, take at least 40GB, but take into account that this is the absolute minimum!
2. Enable the DTA Purge and Archive job
The DTA Purge and Archive job in SQL Server will clear completed and failed messages after a certain given time. We make a separation here between 2 situations: a situation where there is no problem yet with the database and one where we’re struggling with a huge DTA database and disk space issues:
No db problem, just precautionary
By default, the DTA Purge and Archive job will call the BackupAndPurge stored procedure. This call to the stored procedure will take some parameters:
exec dtasp_BackupAndPurgeTrackingDatabase
1, --@nLiveHours tinyint, --Any completed instance older than the live hours +live days
0, --@nLiveDays tinyint = 0, --will be deleted along with all associated data
1, --@nHardDeleteDays tinyint = 0, --all data older than this will be deleted.
'[path to the backuplocation]', --@nvcFolder nvarchar(1024) = null, --folder for backup files
null, --@nvcValidatingServer sysname = null,
0 --@fForceBackup int = 0 –
The first 3 parameters indicate the following: Amount of hours that a completed instance will be kept in the database; Amount of days that a completed instance will be kept in the database; Amount of days that a failed instance will be kept in the database.
The first 2 (both for completed instances) will be added up, so you can configure to keep completed instances for example for 2 days and 3 hours. Everything after this time will be removed.
DB problem, disk space issues
When struggling with a huge DTA db and disk space issues, the above stored procedure which is called might give problems, because the backup of the db can’t be made anymore since there is no more space left on the disk to put the backup on.
When doing this, just to be better safe than sorry, stop all BizTalk services while this script is running and enable them again after step 3.
In this case, we need to change the DTA Purge and Archive job. Instead of calling the PurgeAndArchive procedure, we will call the Purge procedure, without having to deal with a backup of the database.
Replace the original procedure call in the step of the DTA Purge and Archive job by this:
declare @dtLastBackup datetime
set @dtLastBackup = GetUTCDate()
exec dtasp_PurgeTrackingDatabase 1, 0, 1, @dtLastBackup
And after having done this, run the job. BEWARE! This can take quite some time to finish if this script hasn’t run for quite some time. Give it some time to finish (in my case, it took about 3min to finish and it wasn’t that long since that script had run).
3. Shrink the database
This is a step that is often forgotten, but after doing the Purge of the database, we’re not finished yet. The purge will clear the rows that were filled in the DTA db, but will not remove the empty rows itself. Therefore, you might see a little change in size, but not the big change that we’re looking for.
What really matters here is shrinking the DTA database. This will remove all empty rows and elements in the database which are not in use anymore. This will free up a significant amount of space and shrink the database significantly.
Just right click on the BizTalkDTAdb and choose Tasks – Shrink – Database as pictured below.
You will be presented with a window where you can see how much space exactly will be cleared. In my case here, only 158MB will be cleared since I’ve already optimized my DB.
Just click OK and let it do its work. BEWARE again: also this can take a lot of time. In my optimization scenario, I had 2,5GB that would be cleaned and took me up to 10 minutes to execute, so be patient, it will be worth the wait.
After this is done, the window will just disappear.
In case you’ve disabled your BizTalk services/Host Instances, you can restart them here again.
4. Finish it up
Just do a quick check on the size of your database. Normally, you should see a huge difference in db size, depending how much affected your database was of course.
When having any problems or questions, shoot in the comments.
Author: Andrew De Bruyne
