?
Solved

Biztalk2004 SQL 2000 Database maintenance BizTalkMsgBoxDB

Posted on 2006-06-29
6
Medium Priority
?
589 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
6 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 17017306
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
ID: 17020174
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
ID: 17020252
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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 43

Accepted Solution

by:
Eugene Z earned 2000 total points
ID: 17021906
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
ID: 17060837
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
ID: 21179969
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

771 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