Which BizTalk host is which ?

Don't you just hate it that if you want to restart a host in BizTalk 2004 and you look into the task manager because it is taking too much memory that you can't see directly what host to restart ?

I have written a little Console application to help with that problem and decided to share it with the world : EnumerateBizTalkProcesses.exe.

It shows you the memory consumption and processid of all running bizTalk hosts :

Here's the code (it's still .Net 1.1, i haven't tested it with 2.0) :

using System;
using System.Management;
using System.Runtime.InteropServices;
using System.Diagnostics;
using System.Net;
namespace EnumerateBiztalkProcesses
{
///

/// Summary description for Class1.
///

class EnumerateBiztalkProcesses
{
///

/// The main entry point for the application.
///

[STAThread]
private static void Main(string[] args)
{
try
{
uint nStdHandle = 0xfffffff5;
IntPtr hConsoleOutput = GetStdHandle(nStdHandle);
EnumerationOptions options = new EnumerationOptions();
options.ReturnImmediately = false;
string hostName = Dns.GetHostName();
ManagementObjectSearcher searcher = new ManagementObjectSearcher(@"root\MicrosoftBizTalkServer", "Select * from MSBTS_HostInstance where HostType = 1 and ServiceState = 4 and RunningServer = '" + hostName + "'", options);
//Todo : Check to see if any BizTalk host is running !
SetConsoleTextAttribute(hConsoleOutput, 15);
Console.WriteLine("BiztalkHost ProcessId Memory ");
Console.WriteLine("--------------------------------------------------------------------------------");
foreach (ManagementObject obj2 in searcher.Get())
{
string text2 = (string) obj2["HostName"];
PerformanceCounter counter = new PerformanceCounter("BizTalk:Messaging", "ID Process", true);
counter.InstanceName = text2;
float num3 = counter.NextValue();
Process processById = Process.GetProcessById(Convert.ToInt32(num3));
Console.WriteLine(string.Concat(new object[] { text2, new string(' ', 40 - text2.Length), num3, new string(' ', 15 - Convert.ToString(num3).Length), string.Format("{0:0,0} Kb", Convert.ToInt32((int) (processById.WorkingSet / 0x400))) }));
}
Console.WriteLine();
Console.WriteLine("Press any key to exit ...");
int num2 = Console.Read();
}
catch (Exception exception)
{
Console.WriteLine(exception);
}
}
[DllImport("kernel32.dll")]
public static extern IntPtr GetStdHandle(uint nStdHandle);
[DllImport("kernel32.dll")]
public static extern bool SetConsoleTextAttribute(IntPtr hConsoleOutput, int wAttributes);
}
}

Compile and enjoy ...

Linked Server needed after BizTalk SP2 installation

After installing SP2 of BizTalk 2004 you receive an error in the 'TrackedMessages_Copy_BizTalkMsgBoxDb' SQL job :






Executed as user: INT\s_sqlidb. Could not find server 'VIRIDBTTR\BIZTALKTRACKING' in sysservers. Execute sp_addlinkedserver to add the server to sysservers. [SQLSTATE 42000] (Error 7202) An error occurred while inserting data in the Tracking_Parts1 table [SQLSTATE 42000] (Error 50000). The step failed.


If you have your MsgBoxDb & DTADb on seperate SQL Instances installed and you install SP2 of BizTalk 2004 you need to add a linked server from the MsgBoxDb SQL instance to the DTADb SQL instance :

Make sure you choose 'be made using the login's current security context' on the security tab. Leave the rest by default.

Without having that job run correctly you will not be able to save any message body from HAT !

Backup Cleanup for the 'DTA Purge and Archive' job in BizTalk 2006 & BizTalk 2004 SP2

There's good documentation available for configuring the 'DTA Purge and Archive' functionality in BizTalk.

But what about the backups of the DTA Database ? Are they going to stay there forever ? In the documentation they say that you need to manage the cleanup of the backup files yourself.

Here is what i did :

I added a step in the 'DTA Purge and Archive' job in SQL called 'DeleteBackupHistory'.

• BizTalk 2006 on SQL Server 2005 :

DECLARE @OldestDate VARCHAR(50)

--Delete all backed up files created from the 'Purge&Archive' job (take 1 day more than specified in the job)

SELECT @OldestDate = CAST(DATEADD(d, -8, GETDATE()) AS VARCHAR)

EXECUTE master.sys.xp_delete_file 0,N'\\yourserver\yourshare\DTAArchive\',N'bak',@OldestDate,0

• BizTalk 2004 SP2 on SQL Server 2000 :

Since there is no stored procedure 'xp_delete_file' available in SQL Server you have to write a bit more code to do the job. The main principle is that it is looking in the dta_ArchiveHistory table to get the backups older than a specified date :

USE BizTalkDTADb

DECLARE @OldestDate DATETIMESELECT @OldestDate = DATEADD(d, -8, GETDATE())

SELECT dtTimeStamp, nvcBackupLocation FROM dta_ArchiveHistory WHERE dtTimeStamp < @OldestDate

DECLARE @WhichFile VARCHAR(300)

DECLARE @Cmd VARCHAR(500)

DECLARE cur CURSOR FAST_FORWARD FOR SELECT nvcBackupLocation FROM dta_ArchiveHistory WHERE dtTimeStamp < @OldestDate

OPEN cur
FETCH NEXT FROM cur INTO @WhichFile

WHILE @@FETCH_STATUS = 0

BEGIN

SELECT @cmd = 'del ' + @WhichFile + ' /Q /F'

EXEC master.dbo.xp_cmdshell @cmd, NO_OUTPUT

FETCH NEXT FROM cur INTO @WhichFile

END

CLOSE cur

DEALLOCATE cur