Truncating the BizTalk 2006 Tracking Database

In Truncating the BizTalk 2004 Tracking  Database I discussed how to truncate the tracking database in BizTalk 2004. Over on the BizTalk Gurus forums, user Nick Busy wanted to do the same thing for BizTalk Server 2006 – he’s kindly allowed me to repost his instructions for the community on this blog:

0. Before start, ensure you have got the database admin priveleges on the database

1. Stop all BizTalk Server Host Instances

2. Full backup BizTalkDTADb database (just in case)

3. Make scripts to create views (MANDATORY)

dbo.dtav_ServiceFacts
dbo.dtav_MessageFacts
dbo.dtav_FindMessageFacts

4. Run SQL script:

use BizTalkDTADb
GO

— Drop the Views (before you perform this, ensure you take copies of these views!)
— unfortunately, it’s necessary for SQL 2000, but you can skip it for SQL 2005
Drop View dbo.dtav_ServiceFacts
Drop View dbo.dtav_MessageFacts
Drop View dbo.dtav_FindMessageFacts
Go

— Truncate the necessary Tables
Truncate Table dta_CallChain
Truncate Table dta_DebugTrace
Truncate Table dta_MessageInOutEvents

Truncate Table dta_ServiceInstanceExceptions
Truncate Table dta_ServiceInstances

Truncate Table Tracking_Fragments1
Truncate Table Tracking_Parts1
Truncate Table Tracking_Spool1

Truncate Table dta_MessageFieldValues

— end of the script

5. Update statistics on BizTalkDTADb database

— update statistics
exec sp_updatestats

6. Run the saved scripts (see step 3) to recreate the dropped views from your own environment.

7. Shrink BizTalkDTADb database (sometimes it doesn’t work from GUI, so using sql command will help)

— shrink database
dbcc shrinkdatabase (BizTalkDTADb, 10)

8. Start BizTalk Server Host Instances

9. Configure and enable SQL Agent job “DTA Purge and Archive” (to avoid over-growing the database in the future)

P.S. The script above does not truncate Rule Engine related tables.

Thanks Nick, much appreciated.

Advertisements

5 thoughts on “Truncating the BizTalk 2006 Tracking Database

  1. Thanks For the solution it worked for me
    The Tracking data base has grown to the size fo 160 Gigs GB ran the
    DTA Purge and Archive but it could truncated only 60 GB
    one of the table dta_ServiceInstanceExceptions had 14,096,152 Records.
    it worked for me.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s