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.
![Reblog this post [with Zemanta]](http://img.zemanta.com/reblog_e.png?x-id=3cc73cc9-14b0-469b-ab9f-1c3542e29b19)
Hi,
Why should you truncate the BizTalk Tracking database. You can enable the “DTA Purge and Archive” job which clean the Tracking database.
Or am I wrong?
Regards,
Flashbeir
Flashbeir,
You’re not wrong, however on the few occasions that I needed to truncate the Tracking Database, I’ve either been running a development environment and didn’t need the data or a customer has asked me to zap the data to help clear down Gb’s of unwanted tracking info (normally happens when they discover that they’ve just run out of disk-space on their production environment and didn’t understand what the tracking options were for).
Truncating the database isn’t obviously best practice, but is helpful when you need a ‘quick fix’.
Hello Nick,
Small question concerning the dtasp_PruneTrackingDatabase stored procedure.
We have a major disk problem because off a large DTADb (60GB).
The purge jobs runs and keeps 9 days off data and it does it’s job.
The problem is the table dta_url which contains 29.341.352 records.
Why is this table not cleaned up together with the rest in the dtasp_PruneTrackingDatabase SP ?
Some advise would be much appreciated :)
Greetings
Jan
Hello Nick,
I would like to ask you is your truncate script also for BizTalk 2004?
Regards
Martin
Martin,
This script is for BizTalk 2004, if you’re looking for the 2006 version, see: http://www.modhul.com/2008/04/10/truncating-the-biztalk-2006-tracking-database/
Cheers, Nick.
Hi Nick,
thank you for your answare.
Did you try this for BizTalk 2004 SP2? Table dta_MessageInstances doesn’t exist in SP2.
Regards
Martin