RE: BizTalk 2004, DTA-error
- From: wjzhang@xxxxxxxxxxxxxxxxxxxx ("WenJun Zhang[msft]")
- Date: Mon, 30 Jul 2007 09:12:55 GMT
Hi Daniel,
Usually these errors can be caused due to there has not been any
maintenance done on the MessageBox and DTA databases. If this is indeed the
case, I'd like to send you some information on how to maintain these two
databases.
First of all, please NOTE:
The content below covers how to maintain the BizTalkMsgBoxDB and
BizTalkDTADB databases in BTS2004. It assumes that the user does NOT have
the Archive Purge hotfix for BTS2004 installed on the box (KB hotfix
909072). The content also assumes that the user does NOT have BTS2004 SP2
(since it includes 909072)
If you do have hotfix 909072, then refer to the word document that is
shipped with that hotfix ArchivingandPurgingQFEforBizTalk2004.doc. The
latest version of this doc is available by downloading hotfix 909072.
If they do have SP2, then refer to the SP2 readme for instructions on
Archive/Purge.
BizTalk 2004 Database Maintenance (BizTalkMsgBoxDB and BizTalkDTADB)
MAINTAINING THE BizTalkMsgBoxDB DATABASE:
There are 3 types of data that sit in the BizTalkMsgBoxDB:
1. Message & Service Instance data for processing (message parts,
fragments, service instances, subscriptions etc.). This data is created and
maintained by default. The following jobs are enabled by default and are
required to remove old Message & Service Instance data:
-- MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb - Releases rows that
are associated with dead processes
-- MessageBox_Message_Cleanup_BizTalkMsgBoxDb - Cleans up removed messages
from message box tables
-- MessageBox_Parts_Cleanup_BizTalkMsgBoxDb - Cleans up removed parts from
message box tables
-- PurgeSubscriptionsJob_BizTalkMsgBoxDb - Cleans up subscriptions
2. Tracked Events (tracking data that describes transaction events and
context properties but has no message body). This data is created and
maintained by default. The tracking host (running TDDS subservice) will
pick up this data and ultimately move it to BizTalkDTADB and remove it from
BizTalkMsgBoxDB. Again, as long as default settings are not changed, this
is handled by BizTalk. The tracking host can be set from the host
properties within the BizTalk Administration console.
Make sure one of your hosts has tracking enabled and is started all the
time.
3. Tracked Message Bodies (tracking data that contain the actual message
bodies).
This data is NOT created and maintained by default. This data is only
created if message body tracking is enabled by the user at the port,
pipeline, or orchestration levels.
-- TrackedMessages_Copy_BizTalkMsgBoxDb - This job does not remove this
data from the database. Copies tracked message bodies for tracked messages
into the active
Tracking_Spool, Tracking_Parts, & Tracking_Fragments tables from other
locations within the databases. Once this data is put in these tables, it
is never removed from the database and can cause database growth over time.
So the real maintenance for the BizTalkMsgBoxDB is for the Tracked Message
Bodies. If you are not using any message body tracking, then you don¡¯t
have to worry about this. If you are, then you have the choice to simply
purge the Tracked Message Bodies from the database or to archive them to
the file system:
-- If you want to simply purge, you can use the
TrackingSpool_Cleanup_BizTalkMsgBoxDb which is not enabled by default and
refer to
<http://support.microsoft.com/?id=907661>
for more information. This article explains how this job will purge the
offline tracking spool (Tracking_spool, Tracking_parts, &
Tracking_fragments tables) and then make the offline tracking spool online
and the online tracking spool offline. This purge covers Tracking_Spool,
Tracking_Parts, & Tracking_Fragments tables.-- If you want to archive this
data to the file system, you can use the ArchiveMessages.vbs script
mentioned at
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sdk/htm/eb
iz_sdk_samples_qico.asp>
.. This script uses the 3 .fmt files provided with ArchiveMessages.vbs to
create 3 files ("<servername><timestamp>_Tracking_Spool.dat",
<servername><timestamp>Tracking_Parts.dat", and
"<servername><timestamp>_Tracking_Fragments.dat") from the offline tracking
spool
and then purge the offline tracking spool and then make the offline
tracking spool online and the online tracking spool offline. This
archive/purge covers Tracking_Spool, Tracking_Parts, & Tracking_Fragments
tables. ArchiveMessages.vbs and the associated .fmt files must be copied
over to and run from the SQL Server itself or at least a box that has SQL
client tools. The reason is that it uses bcp.exe in the background. You
can't really do anything with these .dat files until they are restored into
a DTA database at which point HAT can be used to view archived message
bodies. This is discussed in the next section.
MAINTAINING THE BizTalkDTADB DATABASE:
The BizTalkDTADB is never maintained by default. This means that it will
continue to grow indefinitely as Tracked Events from the BizTalkMsgBoxDB
are copied over into this database by the tracking host.
There are 3 ways in which to handle this:
1. Purge everything - this is not usually part of a maintenance plan since
it blindly purges everything from the database - this is documented at
<http://support.microsoft.com/?id=894253>
and is usually used to remove everything from an oversized BizTalkDTADB
database that has not been properly maintained in the past. This option is
MUCH quicker than option 2 but will still take many hours or a day when the
DTA is many gigs in size. YOU MUST HAVE EMPTY SPACE EQUIVALENT TO THE DATA
FILE (mdf) SIZE ON THE DRIVE HOLDING THE DTA TRANSACTIONA LOG (ldf) FILE IN
ORDER TO RUN THIS SCRIPT - BECAUSE THE TRANSACTION LOG WILL GROW - so if
the DTA mdf file is 20gb in size, the drive holding the DTA ldf should have
at least 20gb empty space for this purge to work.
2. Purge everything older than a specific date - this is done by the
dtasp_PruneTrackingDatabase which is created by the Purge_DTADB.sql script
mentioned at
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sdk/htm/eb
iz_sdk_samples_qico.asp>
This script is VERY slow compared to option 1 and can take many days to
complete if the DTA is many gigs in size. YOU MUST HAVE EMPTY SPACE
EQUIVALENT TO THE DATA FILE (mdf) SIZE ON THE DRIVE HOLDING THE DTA
TRANSACTION LOG (ldf) FILE IN ORDER TO RUN THIS SCRIPT - BECAUSE THE
TRANSACTION LOG WILL GROW - so if the DTA mdf file is 20gb in size, the
drive holding the DTA ldf should have at least 20gb empty space for this
purge to work.
As an example, if you want to purge anything older than 30 days, this
stored proc can be called as follows:
DECLARE @prunedate datetime
SET @prunedate = GETDATE() - 30
EXEC dtasp_PruneTrackingDatabase @prunedate
3. Create an archive BizTalkDTADB - This is actually done by simply backing
up the BizTalkDTADB to create an archive database and then using options 1
or 2 to purge
from the BizTalkDTADB that BizTalk is pointing to. We can restore the
backup file to a new SQL Server to create an accessible archive tracking
database. Once we have the archive database, if we archived the message
bodies from the BizTalkMsgBoxDB database to the file system in our
BizTalkMsgBoxDB maintenance program, we can use
RestoreMessages.sql script in order to restore those message bodies into
the archive tracking database. This script as well as the steps to use it
are at
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/sdk/htm/eb
iz_sdk_samples_qico.asp>
If we want to view tracked events or tracked message bodies in the archive
database using HAT, you can simply go to Tools > Preference in HAT and
choose Archive data and fill in the archive SQL Server & database names.
All of the above provides the framework for BTS2004 database maintenance.
Rather than doing all of the above manually, custom SQL Agent jobs would
have to be created by the user to do all of the above in an automated
manner in order to have a complete BTS2004 database maintenance program
that will keep your databases maintained while not interfering with BizTalk
processing.
I hope the information above helps.
Have a good day.
Sincerely,
WenJun Zhang
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to:
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at:
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
.
- Prev by Date: Sorting the contents of messages
- Next by Date: Role of BAM?
- Previous by thread: Sorting the contents of messages
- Next by thread: Role of BAM?
- Index(es):
Relevant Pages
|
