BizTalkDTADb

Hi Nick,

Last night I successfully truncate the BizTalkDTADb database. By the way, I found that views with SCHEMABINDING and truncate tablecommand work different in SQL 2000 and SQL 2005. When I tested the script on SQL 2005 there wasn't any issue, but SQL 2000 refused truncate a table without droping the views (that's probably why you did it).

As a result, steps are:

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.

Regards,

Nick Busy