• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 650
  • Last Modified:

Biztalk2004 SQL 2000 Database maintenance BizTalkMsgBoxDB

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
athelu
Asked:
athelu
  • 3
  • 2
1 Solution
 
Eugene ZCommented:
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
 
atheluAuthor Commented:
All of the above jobs are present and running.

BizTalkMsgBoxDB is 30GB (27GB Used)
BizTalkMsgBoxDB _log is 9GB (4GB) used
0
 
atheluAuthor Commented:
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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
Eugene ZCommented:
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
 
atheluAuthor Commented:
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
 
cormaclwoodsCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now