As service instances (receive ports, orchestrations, send ports) process messages moving through BizTalk Server, these service instances can be in one of several states.
In Breakpoint: An active orchestration hits a breakpoint, typically one set by a BizTalk Server solutions developer. This state is valid only for orchestrations.
Ready to run: A service instance that has been activated but has not yet started running, typically due to temporary unavailability of resources, such as a heavy processing load on the server. Active Running service instance.
Dehydrated: The instance state persists in the MessageBox database, and no Windows service is running that instance.
Completed with discarded messages: The service instance was completed, but some messages were not consumed by the instance.
Suspended (resumable): Instance is suspended, you can resume it.
Important: Resuming a messaging instance will do the following:
Resume the messaging instance.
Send the message to the send port. The send port delivers the message to the destination, even if the send port is not in a Started state.
Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
Suspended (not-resumable): The instance is suspended, but you cannot resume it. You can save the Messages referenced by the instance, and then you can terminate the instance.
Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
Pending suspend/Pending terminate: A status, not an independent state. You can combine it with other states.
A control message to suspend or terminate was sent to a service instance but has not yet been picked up by the instance. Only one pending operation is allowed at a time. When an instance with a pending operation becomes dehydrated, you can terminate the instance.
We can see all of these service instances in all different stages inside the BizTalk Server Administration Console:
The problem with the admin console is that it needs manual intervention to check all of this information, so we need to keep clicking F5 each time we want to check them. That is not an effective monitoring operation and quite a waste of human resource time. Of course, there are several other ways to archive this goal:
You can use third-party monitoring tools;
You can use PowerShell to extract this information;
or you can use a simple SQL query.
Well, here is how you do a simple SQL query to count the number of Non-Resumable suspended service instances:
USE BizTalkMsgBoxDb;
SELECT COUNT(nstate) SuspendedNonResumable
FROM Instances WITH (NOLOCK)
WHERE nstate = 3
You need to run this script against BizTalkMsgBoxDB, and it will return the count of instances Suspended that cannot be resumable. Equal to what you see in the BizTalk Server Administration Console.
As service instances (receive ports, orchestrations, send ports) process messages moving through BizTalk Server, these service instances can be in one of several states.
In Breakpoint: An active orchestration hits a breakpoint, typically one set by a BizTalk Server solutions developer. This state is valid only for orchestrations.
Ready to run: A service instance that has been activated but has not yet started running, typically due to temporary unavailability of resources, such as a heavy processing load on the server. Active Running service instance.
Dehydrated: The instance state persists in the MessageBox database, and no Windows service is running that instance.
Completed with discarded messages: The service instance was completed, but some messages were not consumed by the instance.
Suspended (resumable): Instance is suspended, you can resume it.
Important: Resuming a messaging instance will do the following:
Resume the messaging instance.
Send the message to the send port. The send port delivers the message to the destination, even if the send port is not in a Started state.
Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
Suspended (not-resumable): The instance is suspended, but you cannot resume it. You can save the Messages referenced by the instance, and then you can terminate the instance.
Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
Pending suspend/Pending terminate: A status, not an independent state. You can combine it with other states.
A control message to suspend or terminate was sent to a service instance but has not yet been picked up by the instance. Only one pending operation is allowed at a time. When an instance with a pending operation becomes dehydrated, you can terminate the instance.
We can see all of these service instances in all different stages inside the BizTalk Server Administration Console:
The problem with the admin console is that it needs manual intervention to check all of this information, so we need to keep clicking F5 each time we want to check them. That is not an effective monitoring operation and quite a waste of human resource time. Of course, there are several other ways to archive this goal:
You can use third-party monitoring tools;
You can use PowerShell to extract this information;
or you can use a simple SQL query.
Well, here is how you do a simple SQL query to count the number of Resumable suspended service instances:
USE BizTalkMsgBoxDb;
SELECT COUNT(nstate) SuspendedResumable
FROM Instances WITH (NOLOCK)
WHERE nstate = 4
You need to run this script against BizTalkMsgBoxDB, and it will return the count of instances Suspended that can be resumable. Equal to what you see in the BizTalk Server Administration Console.
As service instances (receive ports, orchestrations, send ports) process messages moving through BizTalk Server, these service instances can be in one of several states.
In Breakpoint: An active orchestration hits a breakpoint, typically one set by a BizTalk Server solutions developer. This state is valid only for orchestrations.
Ready to run: A service instance that has been activated but has not yet started running, typically due to temporary unavailability of resources, such as a heavy processing load on the server. Active Running service instance.
Dehydrated: The instance state persists in the MessageBox database, and no Windows service is running that instance.
Completed with discarded messages: The service instance was completed, but some messages were not consumed by the instance.
Suspended (resumable): Instance is suspended, you can resume it.
Important: Resuming a messaging instance will do the following:
Resume the messaging instance.
Send the message to the send port. The send port delivers the message to the destination, even if the send port is not in a Started state.
Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
Suspended (not-resumable): The instance is suspended, but you cannot resume it. You can save the Messages referenced by the instance, and then you can terminate the instance.
Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
Pending suspend/Pending terminate: A status, not an independent state. You can combine it with other states.
A control message to suspend or terminate was sent to a service instance but has not yet been picked up by the instance. Only one pending operation is allowed at a time. When an instance with a pending operation becomes dehydrated, you can terminate the instance.
We can see all of these service instances in all different stages inside the BizTalk Server Administration Console:
The problem with the admin console is that it needs manual intervention to check all of this information, so we need to keep clicking F5 each time we want to check them. That is not an effective monitoring operation and quite a waste of human resource time. Of course, there are several other ways to archive this goal:
You can use third-party monitoring tools;
You can use PowerShell to extract this information;
or you can use a simple SQL query.
Well, here is how you do a simple SQL query to extract Dehydrated service instances:
USE BizTalkMsgBoxDb;
SELECT COUNT(nstate) Dehydrated
FROM Instances WITH (NOLOCK)
WHERE nstate = 8
You need to run this script against BizTalkMsgBoxDB, and it will return the count of instances Dehydrated. Equal to what you see in the BizTalk Server Administration Console.
As service instances (receive ports, orchestrations, send ports) process messages moving through BizTalk Server, these service instances can be in one of several states.
In Breakpoint: An active orchestration hits a breakpoint, typically one set by a BizTalk Server solutions developer. This state is valid only for orchestrations.
Ready to run: A service instance that has been activated but has not yet started running, typically due to temporary unavailability of resources, such as a heavy processing load on the server. Active Running service instance.
Dehydrated: The instance state persists in the MessageBox database, and no Windows service is running that instance.
Completed with discarded messages: The service instance was completed, but some messages were not consumed by the instance.
Suspended (resumable): Instance is suspended, you can resume it.
Important: Resuming a messaging instance will do the following:
Resume the messaging instance.
Send the message to the send port. The send port delivers the message to the destination, even if the send port is not in a Started state.
Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
Suspended (not-resumable): The instance is suspended, but you cannot resume it. You can save the Messages referenced by the instance, and then you can terminate the instance.
Note that when you suspend a scheduled instance and then resume it, the instance goes into a dehydrated state.
Pending suspend/Pending terminate: A status, not an independent state. You can combine it with other states.
A control message to suspend or terminate was sent to a service instance but has not yet been picked up by the instance. Only one pending operation is allowed at a time. When an instance with a pending operation becomes dehydrated, you can terminate the instance.
We can see all of these service instances in all different stages inside the BizTalk Server Administration Console:
The problem with the admin console is that it needs manual intervention to check all of this information, so we need to keep clicking F5 each time we want to check them. That is not an effective monitoring operation and quite a waste of human resource time. Of course, there are several other ways to archive this goal:
You can use third-party monitoring tools;
You can use PowerShell to extract this information;
or you can use a simple SQL query.
Well, here is how you do a simple SQL query to extract active (running) service instances:
USE BizTalkMsgBoxDb;
SELECT COUNT(nstate) Active
FROM Instances WITH (NOLOCK)
WHERE nstate = 2
You need to run this script against BizTalkMsgBoxDB, and it will return the count of active (running) service instances. Equal to what you see in the BizTalk Server Administration Console.
All of these tasks, including the repair task that I’m addressing here, are available and can be, and should be, executed from the BizTalk Health Monitor tool. For those who are not aware of BHM, BHM is an MMC snap-in that allows the user to monitor the health of BizTalk Server environments and execute maintenance tasks. It is, in a simple way, an aggregator of two familiar tools for BizTalk Server: the old MsgBox Viewer and the old BizTalk Terminator.
However, once again, have you ever been curious to know how you can repair these types of messages? Or did you already face the issue that you cannot open the BizTalk Health monitor because it is failing or you don’t have an Internet connection to update them to the last version? Or maybe you are using an environment older than BizTalk Server 2013?
Well, now you have it here:
TRUNCATE TABLE MessageRefCountLog1
TRUNCATE TABLE MessageRefCountLog2
TRUNCATE TABLE MessageRefCountLogTotals
TRUNCATE TABLE MessageZeroSum
UPDATE ActiveRefCountLog SET tnActiveTable = 1 WHERE fType = 1
OPEN curse
FETCH NEXT FROM curse INTO @nvcAppName
WHILE (@@FETCH_STATUS = 0)
BEGIN
-- Truncate local application refcount log if it exists
EXEC('if exists (select * from dbo.sysobjects where id = object_id(N''[dbo].[' + @nvcAppName + '_MessageRefCountLog]'') and OBJECTPROPERTY(id, N''IsUserTable'') = 1) TRUNCATE TABLE [' + @nvcAppName + '_MessageRefCountLog]')
-- Find all the message refs which have no associated row in the spool
EXEC ('INSERT INTO MessageRefCountLog1 (uidMessageID, snRefCount) SELECT uidMessageID, COUNT(*) FROM [' + @nvcAppName + 'Q] GROUP BY uidMessageID')
EXEC ('INSERT INTO MessageRefCountLog1 (uidMessageID, snRefCount) SELECT uidMessageID, COUNT(*) FROM [' + @nvcAppName + 'Q_Suspended] GROUP BY uidMessageID')
EXEC ('INSERT INTO MessageRefCountLog1 (uidMessageID, snRefCount) SELECT uidMessageID, COUNT(*) FROM [InstanceStateMessageReferences_' + @nvcAppName + '] GROUP BY uidMessageID')
FETCH NEXT FROM curse INTO @nvcAppName
END
CLOSE curse
DEALLOCATE curse
INSERT INTO MessageRefCountLog1 (uidMessageID, snRefCount)
SELECT uidMessageID, COUNT(*)
FROM [TrackingMessageReferences] GROUP BY uidMessageID
-- Inserting restart message details, for service recovery
IF NOT EXISTS (SELECT TOP 1 uidMessageID FROM Spool WHERE uidMessageID = N'61EAA7FC-AC85-42d9-BF3E-1BED258B82BE')
BEGIN
INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N'61EAA7FC-AC85-42d9-BF3E-1BED258B82BE', 0, 0, 0xD4E0906C1849D311A24200C04F60A53302000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F007000650072007400690065007300000001000000010820010000000000000000000700000098000000540069006D0065007200490044005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F0078006C0061006E00670073002D00720075006E00740069006D0065002D00700072006F0070006500720074006900650073000000010000000008004A000000300030003000300030003000300030002D0030003000300030002D0030003000300030002D0030003000300030002D003000300030003000300030003000300030003000300030000000)
END
-- Inserting Suspend Control message
IF NOT EXISTS (SELECT TOP 1 uidMessageID FROM Spool WHERE uidMessageID = N'2BE3D5B8-5685-40F2-BD97-51ADA3D02347')
BEGIN
INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N'2BE3D5B8-5685-40F2-BD97-51ADA3D02347', 0, 0, 0xD4E0906C1849D311A24200C04F60A53303000000000000001E000000420069007A00540061006C006B0043006F006E00740072006F006C000000020000000008002A000000410064006D0069006E00530075007300700065006E00640049006E007300740061006E00630065000000000000000C0000004A006F006200490044000000020000000008004E0000007B00340037004100450033003300380034002D0031003000410041002D0034003400430033002D0038003200350036002D003200350033003400380045004200390032004200320031007D000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F00700065007200740069006500730000000100000001082001000000000000000000)
END
-- Inserting Terminate Control message
IF NOT EXISTS (SELECT TOP 1 uidMessageID FROM Spool WHERE uidMessageID = N'57E5E753-0207-435D-8BE7-2B9F3C6556F9')
BEGIN
INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N'57E5E753-0207-435D-8BE7-2B9F3C6556F9', 0, 0, 0xD4E0906C1849D311A24200C04F60A53303000000000000001E000000420069007A00540061006C006B0043006F006E00740072006F006C000000020000000008002E000000410064006D0069006E005400650072006D0069006E0061007400650049006E007300740061006E00630065000000000000000C0000004A006F006200490044000000020000000008004E0000007B00350032003500390031004600310031002D0046003700370034002D0034003600330038002D0042004300390041002D003200380034003800380034003600300034003500450032007D000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F00700065007200740069006500730000000100000001082001000000000000000000)
END
-- Inserting Resume in Debug Mode message
IF NOT EXISTS (SELECT TOP 1 uidMessageID FROM Spool WHERE uidMessageID = N'50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3')
BEGIN
INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N'50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3', 0, 0, 0xD4E0906C1849D311A24200C04F60A53303000000000000001E000000420069007A00540061006C006B0043006F006E00740072006F006C0000000200000000080026000000410064006D0069006E004400650062007500670049006E007300740061006E00630065000000000000000C0000004A006F006200490044000000020000000008004E0000007B00330032003800330030004500450045002D0035004400330041002D0034003700370037002D0041003200440035002D003100320033004100450035004600340044004500420039007D000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F00700065007200740069006500730000000100000001082001000000000000000000)
END
--we need to add refcounts for the control messages in our system.
INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N'61EAA7FC-AC85-42d9-BF3E-1BED258B82BE', 1)
INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N'2BE3D5B8-5685-40F2-BD97-51ADA3D02347', 1)
INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N'57E5E753-0207-435D-8BE7-2B9F3C6556F9', 1)
INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N'50D173AF-5D6F-4D5F-AE23-1A7178CEBDC3', 1)
--lets run the stored procedure to process all of this and add up the values to put them in the totals table
EXEC int_PurgeMessageRefCountLog 1, @ret OUTPUT
--Now we have everything in the totals table (nothing is in the zero sum table since we only added references to messages
--currently in the messagebox. There were no releases (ie negative numbers)
--Lets populate the zerosum table with any messages in the spool which are not in the totals table
INSERT INTO MessageZeroSum (uidMessageID)
SELECT uidMessageID FROM Spool s WHERE s.uidMessageID NOT IN (SELECT uidMessageID FROM MessageRefCountLogTotals)
Note: This is part of the script. If you want the full script download it from the link available at the end of the post.
This script rebuilds all the MessageRefCountLog tables and the MessageZeroSum table to resolve the ‘Messages w/out RefCounts’ issue that MessageBoxViewer identifies. While doing this, control messages are rebuilt if needed.
WARNINGS:
THIS OPERATION WILL PERMANENTLY REMOVE OR ALTER DATA IN YOUR DATABASE.
Before running this script, make sure that:
All BizTalk databases should be backed up
All host instances should be stopped
All BizTalk SQL Agent jobs should be stopped.
THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.
Once again, if possible, run these scripts and execute these tasks inside the BizTalk Health Monitor tool.
I recently received a request for help from a customer about a problem that has started to occur with SQL Server jobs in BizTalk Server. Everything was working fine for the last few months until they begin receiving the following errors while the SQL Jobs was trying to execute:
The job failed. Unable to determine if the owner (domainusername) of job MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb has server access (reason: Could not obtain information about Windows NT group/user ‘domainusername’, error code 0x534. [SQLSTATE 42000] (Error 15404)).
Cause
Actually, the reason for this error was quite simple, and it is an error that can happen relatively often if we do not take the necessary steps.
In this case, the user that performed the BizTalk Server installation and configuration was a personal account that was a member of the BizTalk Server Administration group, and by default, he is configured as the owner of that jobs. However, at some point, the employee left or terminated the contract and his account was terminated or disabled and that is the reason for that error started to happen.
Solution
The solution is simple, we need to change the owner. And to do that, we need:
Open the SQL Server Management Studio.
Expand SQL Server Agent, and then Jobs.
Right-click on job name, in this case, MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb and select Properties
In the Owner field, select the sa account, or any other account, as the job owner using the ellipsis button
Do that steps for all the BizTalk Server SQL Jobs.
