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.
Nick Heppleston
An Independent BizTalk Consultant in the UK - Blog at http://www.modhul.com
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 BizTalkDTADbGO-- Truncate the necessary TablesTruncate Table dta_CallChainTruncate Table dta_DebugTraceTruncate Table dta_MessageInOutEventsTruncate Table dta_ServiceInstanceExceptionsTruncate Table dta_ServiceInstancesTruncate Table Tracking_Fragments1Truncate Table Tracking_Parts1Truncate Table Tracking_Spool1Truncate Table dta_MessageFieldValues-- update statisticsexec 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,
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.
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 BizTalkDTADbGO-- 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 2005Drop View dbo.dtav_ServiceFactsDrop View dbo.dtav_MessageFactsDrop View dbo.dtav_FindMessageFactsGo-- Truncate the necessary TablesTruncate Table dta_CallChainTruncate Table dta_DebugTraceTruncate Table dta_MessageInOutEventsTruncate Table dta_ServiceInstanceExceptionsTruncate Table dta_ServiceInstancesTruncate Table Tracking_Fragments1Truncate Table Tracking_Parts1Truncate Table Tracking_Spool1Truncate Table dta_MessageFieldValues-- end of the script
5. Update statistics on BizTalkDTADb database
-- update statisticsexec 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 databasedbcc 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.
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.
_=Prodigy=_