Re: Purge a very big Tracking database

Hi All,

I've got a very huge BizTalkDTADb database because DTA Purge was disabled for a long period of time. Running the job now is not a decision.

I've seen a  script to truncate tables in the DTA database for BizTalk 2004. I made some changes to it to apply it to BizTalk 2006 database. My question is, did anyone try to truncate tracking tables without any problem? If there was a ready to go script, I would consider it.

In the SQL-script I have found, they drop some views before truncating the tables and recreate them (views) right after truncate. I am wondering about a reason of doing so. Why should I drop and recreate views if I want to truncate tables?

Any ideas?

Thanks,

Nick 

Nick,

I've successfully used the truncate script on my blog (http://www.modhul.com/2008/01/03/truncating-the-biztalkdtadb-database/) to truncate the BizTalk 2006 Tracking Database. Unfortunately, I can't remember why I dropped the views and re-created them.

Nick.

 Hi Nick,

Thanks for reply and for your post in the blog. I saw it when was looking for a script.

I reckon, you run it for BizTalk 2004, because they changed tables in 2006. Now, table dta_MessageInstances does not exist, but there are some other in place.

I changed the script it looks working fine for me with BizTalk 2006 database (I did not touch rule engine related tables because it's not an issus in my case):

use BizTalkDTADb
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

-- update statistics
exec sp_updatestats

-- end of the script

I also tried to <exec sp_refreshview> for the related views, but it did not work because of SCHEMABINDING. The same reason will not allow you to drop the tables. May be that's why you dropped views and re-created them. Anyway, I decided running refreshview is not necessary since I don't change structure of the tables but only remove data from them.

As I said, I tested the script with success in development environment. I am changing production environment next Tuesday, and will see how it goes. Of course, I will make a full backup before changing :)

Regards,

Nick 

Nick,

If you find that the truncate works in BizTalk 2006 please let me know and I will update my blog accordingly (giving full credit to yourself for the work) - I see this problem come up time-and-time again - it'd be good to give it back to the community.

Cheers, Nick.

Hi Nick,

Last night I successfully truncate the BizTalkDTADb database. By the way, I found that views with SCHEMABINDING and truncate table command 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

Thanks Nick, I've updated my blog post at this link above.

Had your same situation and needed the DB clean last week. There is a stored proc in the DTA db named something along the lines of "purge all completed transactions"

I just run that as needed then shrink the log. Its a good way to clean the DB while still running in production when you dont have an option on taking your servers into maintenence.

Its relativly lightweight and although youll see it drop performance a bit, youll still be moving data at a good speed.