Close

BizTalk terminate instances

Recently I had a problem that I couldn’t delete a running instance in BizTalk. So, I tried to install the BizTalkTerminator, but I kept getting an error when trying to start up the terminator. Luckily I found a SQL script you can run to reslove the issue. Remember to first stop all BizTalk instances and then you’re all set to go.

Hard Terminate:

/*
First stop all hosts
*/

USE BizTalkMsgBoxDb

declare @ApplicationName nvarchar(128)
declare @uidInstanceID uniqueidentifier
declare @uidServiceID uniqueidentifier
declare @fKnownInstance int
declare @spname nvarchar(512)

/*
SELECT TOP 1000 [uidInstanceID],[uidServiceID] FROM [BizTalkMsgBoxDb].[dbo].[Instances] */

Begin Tran
set @ApplicationName=’BizTalkServerIsolatedHost’
set @uidInstanceID=’61E8C8A5-FA59-4A77-8B10-14FC80817D71′
set @uidServiceID=’69A8D1B9-0832-40B6-AA7F-3553A156B660′
set @spname=’int_AdminTerminateInstance_’ + @ApplicationName
exec @spname @uidInstanceID, @uidServiceID, @fKnownInstance OUTPUT
DELETE FROM InstancesPendingOperations WITH (ROWLOCK) WHERE uidInstanceID = @uidInstanceID OPTION (KEEPFIXED PLAN)
Commit Tran

You can also suspend. Don’t know why you wanna take this extra step, but just for completeness.

Hard Suspend:

declare @ApplicationName nvarchar(128)
declare @uidInstanceID uniqueidentifier
declare @uidServiceID uniqueidentifier
declare @fKnownInstance int
declare @nvcErrorString nvarchar(512)
declare @dtTimeStamp datetime
declare @spname nvarchar(512)

Begin Tran
set @ApplicationName=’BizTalkServerIsolatedHost’
set @uidInstanceID=’61E8C8A5-FA59-4A77-8B10-14FC80817D71′
set @uidServiceID=’69A8D1B9-0832-40B6-AA7F-3553A156B660′
set @dtTimeStamp=GETUTCDATE()
select @nvcErrorString = nvcError FROM dbo.LocalizedErrorStrings WHERE nID = 4
set @spname= ‘int_AdminSuspendInstance_’ + @ApplicationName
exec @spname @uidInstanceID, @uidServiceID, N’0xC0C01B50′, -1, @nvcErrorString, 1, null, @dtTimeStamp, null, null, @fKnownInstance OUTPUT
DELETE FROM InstancesPendingOperations WITH (ROWLOCK) WHERE uidInstanceID = @uidInstanceID OPTION (KEEPFIXED PLAN)
Commit Tran