Solved

Biztalk2004 SQL 2000 Database maintenance BizTalkMsgBoxDB

Posted on 2006-06-29
6
583 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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

726 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