Well our BizTalk Server tracking database was growing large and the Job that was configured to purge and Archive was not running well.
Use the following to purge.
–– Change to the BizTalk Tracking database.
–– Purge tracking data older than two hours.
DECLARE @dtLastBackup DATETIME
SET @dtLastBackup = GetUTCDate()
EXEC dtasp_PurgeTrackingDatabase 2, 0, 1, @dtLastBackup
As I started executing this statement. It took a while and I could notice the drastic change in my BiztalkDTADb_log.ldf located it C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA.
It has started to grow to almost the size of my mdf file. When I thought it was done, Damn!! By log file grew out of the max limit size and threw an error.
Well, We would have to work on this task again.
Additionally, this article explains in detail on how to truncate tracking database.
If you ever want to plan for Disaster recovery, consider reading this article