Solved

Biztalk2004 SQL 2000 Database maintenance BizTalkMsgBoxDB

Posted on 2006-06-29
6
565 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
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 42

Accepted Solution

by:
EugeneZ 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
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.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

832 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