Nick Heppleston’s BizTalk Blog

This post relates to truncating the BizTalk 2004 Tracking Databases, for BizTalk 2006, see http://www.modhul.com/2008/04/10/truncating-the-biztalk-2006-tracking-database/

–

Update: I’ve had a few questions as to why the views are dropped: these views use the SCHEMABINDING option – creating a view with SCHEMABINDING locks the tables being referred by the view and prevents any changes that may change the table schema, it also appears to stop the tables from being truncated. Hence the views are dropped, the tables truncated and the views re-created. Note: This functionality only appears to be the case in SQL Server 2000, 2005 does not require the views to be dropped.

–

I’ve noticed that the link I posted to Mike Holdorf’s blog back on my
Host Tracking and the BizTalkMsgBoxDb post no longer works – it would appear that Mikes domain no longer exists.

For posteriety (and because I’ve just had a customer ask me how to do this and I couldn’t remember), here is the script in full:

use BizTalkDTADb
go

– Drop the Views (before you perform this, ensure you take copies of these views!)
Drop View dtav_ServiceFacts
Go
Drop View dtav_MessageFacts
Go
Drop View dtav_FindMessageFacts
Go

– Truncate the necessary Tables
Truncate Table dta_ServiceInstances
Truncate Table dta_ServiceInstanceExceptions
Truncate Table dta_DebugTrace
Truncate Table dta_CallChain
Truncate Table dta_MessageInstances
Truncate Table dta_MessageInOutEvents
Truncate Table dta_MessageFieldValues
Truncate Table dta_MessageFields

– Recreate the dropped views from your own environment.