BizTalk and SQL Server 2000

We're running BizTalk 2004 on top of SQL Server 2000. Due to the fact that BizTalk uses Distributed Transactions BizTalk requires a customized backup solution that uses the BEGIN TRANSACTION ... WITH MARK syntax. How does the WITH MARK work? We run a couple of very large transaction loads at various times during the week. These loads ran the BizTalkMsgBoxDb transaction log up to 18 Gig (the data file was only at 360 Meg). As the transaction log got bigger and bigger the server got slower and slower. Finally the custom backup job started erroring out on a memory error. Eventually the backup jobs stopped running due to an out of memory condition and then my users got locked out due to an out of memory condition. After I did some research (I rebooted the server which didn't help) I figured out that it was this WITH MARK that appeared to be having the issue. On a hunch I did a DBCC shrinkfile on the BizTalkMsgBoxDb transaction log and I got it down to 506 Meg. All of my problems cleared up. This is fine for the short term but these loads will not only continue they will increase in size and frequency since we are still in our testing phase, we haven't gotten to production yet. Is there a something I've overlooked here?Good info here:
http://msdn2.microsoft.com/en-us/library/ms187014.aspx|||Thank you for the article. What I really need is a very detailed explanation of how a transaction log mark works. Does the system try and open the transaction log like a text file (for example) in order to put a mark in? I've got a couple of databases in BizTalk that have transaction logs that grow rather large and once they get to the 1.5 Gig to 2 Gig size SQL Server starts to throw memory errors (SQL Server Standard Edition) when the TRANSACTION WITH MARK runs. I can get rid of the memory errors by running a backup then issuing a DBCC Shrinkfile but this isn't a viable option for my production system. Since this is BizTalk I was expecting that Microsoft would have a solution to my problem. Have you seen this before?|||

When a 'with mark' is set, the system will start keeping track of every operation between the database(s) involved in the dml session. This can get rather large if there are lots of changes.

So, unless you really need 'with mark' set, don't. Especially, if you are not creating a transaction across multiple databases.

|||Aaaa, but the 'with mark' is part of the custom backup solution that is supplied with BizTalk in order to handle the fact that BizTalk uses Distributed Queries all over the system. So my ultimate question is - how do I take a consistent backup of all of the BizTalk databases so that a restore would leave them in sync.? When I asked this question of some of the folks we had help us install and configure BizTalk they gave me the stored procedure that we run as our backup which uses the "with mark". Is there an alternative?