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.
In the previous post, we analyze how you can monitor messages without reference counts (RefCounts), today it will be a similar topic, but this time we will be addressing messages with negative reference counts.
and the same question applies here:
Have you ever seen your Monitor BizTalk Server (BizTalkMgmtDb) job failing and complaining about the existence of messages with negative reference counts
Or do you ever want to know more about messages with negative RefCounts?
What are messages with negative RefCounts?
Do they show in the BizTalk Server Administration console? Are they impacting BizTalk Server performance?
Indeed messages with negative reference counts can appear from time to time in our environment, which you should definitely need to monitor. And to response to all previous questions:
What are messages with negative RefCounts?
Messages with negative RefCounts are messages in the MessageRefCountLogTotals with snRefCount less than zero. Once a refcount goes negative, the MessageBox cleanup jobs will not be able to clean up the corresponding messages.
Do they show in the BizTalk Server Administration console?
No, they don’t. The only way for you to know that exists messages with negative reference count is by:
Running the Monitor BizTalk Server (BizTalkMgmtDb) job
Executing the View Count of Messages With Negative RefCounts available on the BizTalk Health Monitor (maintenance)
Or executing a custom query against BizTalk Server databases.
Are they impacting BizTalk Server performance?
Yes, if they are too many.
As I mentioned before, the Monitor BizTalk Server SQL Agent job can detect these kinds of messages. In fact, it is able to identify any known issues in Management, MessageBox, or DTA databases. The job scans for the following issues:
Messages without any references
Messages without reference counts
Messages with negative reference counts
Messages with reference count less than 0
Message references without spool rows
Message references without instances
Instance state without instances
Instance subscriptions without corresponding instances
Orphaned DTA service instances
Orphaned DTA service instance exceptions
TDDS is not running on any host instance with the global tracking option enabled.
By default, the Monitor BizTalk Server job is configured and automated to run once in a week. Since the job is computationally intensive, it is recommended to schedule it during downtime/low traffic. The job fails if it encounters any issues; error string contains the number of issues found. Otherwise, it runs successfully.
Note: The Monitor BizTalk Server job only scans for issues. It does not fix the issues found.
However, are you ever curious to know how you can find 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 Internet connection to update them to the last version?
Well, now you have it here:
DECLARE @count bigint
SET @count = 0
SELECT @count = COUNT(*) FROM [dbo].[MessageRefCountLogTotals] WHERE [snRefCount] < 0
IF @count = 0
BEGIN
SELECT 'There are no negative RefCounts'
END
ELSE
BEGIN
SELECT COUNT(*) Count, [snRefCount] as 'RefCount Value'
FROM [dbo].[MessageRefCountLogTotals]
WHERE [snRefCount] < 0
GROUP BY [snRefCount]
ORDER BY [snRefCount]
END
Of course, needless to say, use it with care and thoughtfulness! It is preferred to call this query inside the BizTalk Health Monitor, and ideally, it is recommended to execute it during downtime/low traffic.
You need to run this script against BizTalkMsgBoxDB, and it will return the count of negative refcounts in the MessageRefCountLogTotals table. Equal to the script on the BizTalk Health Monitor.
Have you ever seen your Monitor BizTalk Server (BizTalkMgmtDb) job failing complaining about the existence of messages without reference counts or more know as messages without RefCounts? What are messages without RefCounts? Do they show in the BizTalk Server Administration console? Are they impacting BizTalk Server performance?
Indeed messages without reference counts can appear from time to time in our environment, which you should definitely need to monitor. And to response to all previous questions:
What are messages without RefCounts?
Messages without reference counts (RefCounts) are messages that don’t have correlating rows in the MessageRefCountLog tables and the MessageZeroSum table. Once they are in this state, the MessageBox cleanup job will not be able to clean up the corresponding messages.
Do they show in the BizTalk Server Administration console?
No, they don’t. The only way for you to know that exists messages without reference counts is by:
Running the Monitor BizTalk Server (BizTalkMgmtDb) job
Executing the View COunt of Messages Without RefCounts available on the BizTalk Health Monitor (maintenance)
Or executing a custom query against BizTalk Server databases.
Are they impacting BizTalk Server performance?
Yes, if they are too many.
As I mentioned before, the Monitor BizTalk Server SQL Agent job can detect these kinds of messages, in fact, it is able to identify any known issues in Management, MessageBox, or DTA databases. The job scans for the following issues:
Messages without any references
Messages without reference counts
Messages with reference count less than 0
Message references without spool rows
Message references without instances
Instance state without instances
Instance subscriptions without corresponding instances
Orphaned DTA service instances
Orphaned DTA service instance exceptions
TDDS is not running on any host instance with the global tracking option enabled.
By default, the Monitor BizTalk Server job is configured and automated to run once in a week. Since the job is computationally intensive, it is recommended to schedule it during downtime/low traffic. The job fails if it encounters any issues; error string contains the number of issues found. Otherwise, it runs successfully.
Note: The Monitor BizTalk Server job only scans for issues. It does not fix the issues found.
However, are you ever curious to know how you can find 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 Internet connection to update them to the last version?
Well, now you have it here:
DECLARE @nvcAppName nvarchar(256)
CREATE TABLE ##msgs_wout_refs (uidMessageID uniqueidentifier NOT NULL)
CREATE UNIQUE CLUSTERED INDEX [CIX_msg_wout_refs] ON [##msgs_wout_refs](uidMessageID)
INSERT INTO ##msgs_wout_refs (uidMessageID)
SELECT uidMessageID FROM Spool WHERE uidMessageID NOT IN(SELECT uidMessageID FROM MessageRefCountLogTotals UNION
SELECT uidMessageID FROM MessageRefCountLog1 UNION
SELECT uidMessageID FROM MessageRefCountLog2 UNION
SELECT uidMessageID FROM MessageZeroSum
)
DECLARE hostcursor CURSOR FOR
SELECT nvcApplicationName FROM Applications WITH (NOLOCK)
OPEN hostcursor
FETCH NEXT FROM hostcursor INTO @nvcAppName
WHILE (@@FETCH_STATUS = 0)
BEGIN
EXEC ('DELETE FROM ##msgs_wout_refs FROM ##msgs_wout_refs m, [dbo].[' + @nvcAppName + '_MessageRefCountLog] r WHERE m.uidMessageID = r.uidMessageID')
FETCH NEXT FROM hostcursor INTO @nvcAppName
END
CLOSE hostcursor
DEALLOCATE hostcursor
DECLARE @count bigint
SET @count=0
SELECT @count = count(*) from ##msgs_wout_refs
SELECT 'Messages w/o Refcounts: ' + cast (@count as nvarchar(10))
DROP TABLE ##msgs_wout_refs
Of course, needless to say, use it with care and thoughtfulness! It is preferred to call this query inside the BizTalk Health Monitor, and ideally, it is recommended to execute it during downtime/low traffic.
You need to run this script against BizTalkMsgBoxDB, and it will return the count of messages that don’t have correlating rows in the MessageRefCountLog tables and the MessageZeroSum table and should align with the ‘Messages w/out RefCounts’ issue that MessageBoxViewer identifies. Equal to the script on the BizTalk Health Monitor.
Recently a
friend of mine that is working with me in a project send me an email reporting
a quite curious issue that I found while accessing the BizTalk Server
Administration console in our development environment:
This operation failed while accessing at least one of the Message Bix databases. Some results might be omitted. (Microsoft.Biztalk.Administration.SnapIn)
Additional information:
The transaction log for database ‘BizTalkMsgBoxDb’ is full due to ‘LOG_BACKUP’. (Microsoft SQL Server, Error: 9002)
Immediately I point out to the team that this issue
was related to lack of disk space.
Cause
The official cause of
this issue is that when the transaction log becomes full, SQL Server Database
Engine issues a 9002 error. The log can fill when the database is online, or in
recovery. If the log fills while the database is online, the database remains
online but can only be read, not updated.
And for us to know the exact cause for what is
preventing log truncation, we can use the log_reuse_wait and log_reuse_wait_desc
columns of the sys.database catalog view.
In our case, it was indeed a problem with disk
space and what happened was that the disk to where we were doing backup went out
of disk space, because we cannot do the backups the transaction log grow until
the point that disk (that contain the log file) also went out of disk space.
Solution
When you know the issue, the solution is quite easy. In this case, freeing disk space from both hard drives immediately solves the problem. Nevertheless, because the log file got quite big you should think of stopping your BizTalk Server environment and do maintenance in your databases in special reduce the size of the transaction log.
For that you should:
Perform
a full back of your databases;
Stop
all BizTalk Server services (host instances and enterprise Single Sign-on)
And
run the following SQL Script
ALTER DATABASE BizTalkMsgBoxDb
SET RECOVERY SIMPLE;
GO
DBCC SHRINKFILE (BizTalkMsgBoxDb_log, 2048);
GO
ALTER DATABASE BiztalkMsgBoxDb
SET RECOVERY FULL
GO
Do
about the same steps for other databases whose transaction logs are also quite large.
Finally, BizTalk Server Databases: Disaster Recovery, Troubleshooting and Best Practices whitepaper is published! I think this was my crazy project ever because I started during an MVP Summit, probably in 2013 with a very basic whitepaper of 7 pages about Disaster Recovery and my initial reviewer was Tord Glad Nordahl… for some reason I never finished or published the content and last year again during the MVP Summit I revived this idea and basically annoyed all the MVPs that were next to me that day to review the initial document again… bad idea for me because, probably to get revenge on me, they were the most demanding reviewers that I ever had, always asking for more content making, therefore, the whitepaper more complete. So, what was a 7-pages whitepaper becomes a 34-pages whitepaper.
What to expect about BizTalk Server Databases: Disaster Recovery, Troubleshooting and Best Practices whitepaper
Microsoft BizTalk Server databases and the health of the databases are very important for a successful BizTalk Server messaging environment. BizTalk Server is an extremely database-intensive platform, persisting data to disk with high frequency, and one of the main reasons for that is because one of the primary design goals of BizTalk Server is to ensure that no messages are lost. Therefore, database performance is paramount to the overall performance of any BizTalk Server solution.
There are many factors that you need to take into consideration towards troubleshooting, maintaining, monitoring or recovering from disasters. This paper will provide you some important aspects to consider when working with BizTalk Server databases and addressing the most common and important aspects:
Size of databases and tables: performance degrades on High Size of BizTalk databases
Important consideration to avoid large BizTalk Databases
Separation of data files and log files (SQL Server disk I/O contention)
Important consideration BizTalk SQL Settings
Available tools for monitoring and troubleshooting
Recovering from disasters situations (Clean up your BizTalk databases)
However, the content is very valuable with regards to even preventing a disaster or limit the probability of it. After reading this paper you should be prepared for any disaster but also to preventing for happening because be able to prevent is better than resolving.
I would like to take this opportunity also to say thanks to my amazing reviewers: Steef-Jan Wiggers, Nino Crudele, Kent Weare, Mikael Hakansson and Salvatore Pellitteri for taking the time to review this whitepaper. And other people that were involved in making this “project” came true like Tord Glad Nordahl, Lex Hegt, Saravana Kumar and Sriram Hariharan.
I hope you enjoy reading this paper and any comments or suggestions are welcome.
Author: Sandro Pereira
Sandro Pereira lives in Portugal and works as a consultant at DevScope. In the past years, he has been working on implementing Integration scenarios both on-premises and cloud for various clients, each with different scenarios from a technical point of view, size, and criticality, using Microsoft Azure, Microsoft BizTalk Server and different technologies like AS2, EDI, RosettaNet, SAP, TIBCO etc. He is a regular blogger, international speaker, and technical reviewer of several BizTalk books all focused on Integration. He is also the author of the book “BizTalk Mapping Patterns & Best Practices”. He has been awarded MVP since 2011 for his contributions to the integration community. View all posts by Sandro Pereira
