BizTalk Server monitoring: View Count of Messages With Negative Reference Counts (RefCounts)

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.

Where can I download it?

You can download the SQL script here:

THIS SQL SCRIPT IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND.

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

turbo360

Back to Top