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

This post was originally published here

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.

Where can I download it?

You can download the SQL script here:

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