Solved

Biztalk2004 SQL 2000 Database maintenance BizTalkMsgBoxDB

Posted on 2006-06-29
6
561 Views
Last Modified: 2010-05-18
I have been running biztalk for just over a year now, and thing have been working well. The issue I am having is that the BizTalkMsgBoxDB is getting HUGE. it is currently 30GB. Is there a way that I can safely archive some of the data in this table?
0
Comment
Question by:athelu
  • 3
  • 2
6 Comments
 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
Biztalk installation should generate main db sql server agent jobs -
such us:
MessageBox_Message_Cleanup_BizTalkMsgBoxDb
MessageBox_Parts_Cleanup_BizTalkMsgBoxDb
CleanupBTFExpiredEntriesJob_BizTalkMgmtDb
PurgeSubscriptionsJob_BizTalkMsgBoxDb
TrackingSpool_Cleanup_BizTalkMsgBoxDb
MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb

-------------
what sql agent jobs do you see?
BTW: what the BizTalkMsgBoxDB db files sizes:
Maybe it is time to shrink trans log file of the BizTalkMsgBoxDB ?
0
 
LVL 9

Author Comment

by:athelu
Comment Utility
All of the above jobs are present and running.

BizTalkMsgBoxDB is 30GB (27GB Used)
BizTalkMsgBoxDB _log is 9GB (4GB) used
0
 
LVL 9

Author Comment

by:athelu
Comment Utility
I looked at the logs for the individual jobs and they are running every 1 minute and take no time to complete (<1sec), but I do not see that they are deleting anything.

is there something that needs to be defined on the orchestration to tell it to be pureable?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 42

Accepted Solution

by:
EugeneZ earned 500 total points
Comment Utility
please read the article  about

stored proc -- dtasp_BackupAndPurgeTrackingDatabase


Archiving and Purging the BizTalk Tracking Database
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/BTS06Operations/html/7014cf31-86e8-4829-8055-056442329009.asp
0
 
LVL 9

Author Comment

by:athelu
Comment Utility
This got me on the right track. The actual table that is bloated is tracking_spool1 in my BiztalkMsgBoxDb.
 here is what is recommended per Microsoft: http://support.microsoft.com/?id=907661

0
 

Expert Comment

by:cormaclwoods
Comment Utility
If the message box is growing continuously it probably is not related to tracked messages. It is most likely that the TrackingSpool_Cleanup_BizTalkMsgBoxDb job is not being run. This job needs to run to clear out the spools of the Message box. As far as I know this job is not enabled by default. There are 2 spools in the message box database. Running TrackingSpool_Cleanup_BizTalkMsgBoxDb  causes Biztalk to move from Spool A to spool B. The next time it is run it moves back to spool A. Only at this point does it start to overwrite the messages in the spool. See http://support.microsoft.com/kb/907661
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now