MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb failing

I had a test environment where the SQL Agent job MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb had been failing for a while. This job runs exec bts_CleanupDeadProcesses in the message box DB. It was failing with the error

Executed as user: Domain\Account. Could not find stored procedure ‘dbo.int_ProcessCleanup_OLDHOSTNAME’. [SQLSTATE 42000] (Error 2812). The step failed.

Now the clue in this was that the stored procedure was trying to execute another stored procedure with a name of an a host that had been decommissioned, along with several others, after there had been some issues with them and replacement host and host instances had been created.

In that stored procedure it had this

DECLARE btsProcessCurse CURSOR FAST_FORWARD FOR
SELECT s.uidProcessID, s.nvcApplicationName FROM dbo.ProcessHeartbeats AS s WITH (ROWLOCK READPAST)
WHERE (s.dtNextHeartbeatTime < @dtCurrentTime) OPTION(KEEPFIXED PLAN)

So to see what that was getting I ran the below

declare @dtCurrentTime datetime

set @dtCurrentTime = GetUTCDate()

select *
FROM dbo.ProcessHeartbeats WITH (ROWLOCK READPAST)
WHERE (dtNextHeartbeatTime < @dtCurrentTime) OPTION(KEEPFIXED PLAN)

This gave a list of very old heartbeats for all the old host names. To resolve the issue I changed the select * into a delete, and ran that, and the SQL Agent Job is now running correctly.

Advertisement

Author: Coen (Colin) Dijkgraaf

A Integration Consultant for Datacom in Auckland, New Zealand, working mainly with BizTalk and Azure. You can follow me on Twitter, StackOverflow and WordPress

2 thoughts on “MessageBox_DeadProcesses_Cleanup_BizTalkMsgBoxDb failing”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Mike the Tester

A blog about all things testing. Views are my own

Nick's Blog

Biztalk gotcha!

Whatever

FURIOUSLY REASONABLE

A Different Kind of Query

Technology, music, life, and musings

Vierodan IT Space

Spread up technology

BizTalk Server Help book!

Made easy and simple

nethramysooru

Blog on BizTalk Server

Blog Of the Serverless Spirit

Stray Notions on All Things Microsoft Azure and BizTalk

Microsoft Azure/BizTalk_Read

Let's learn and share !

Boutaleb Hicham

Biztalk & Azure Integration Architect : Logic Apps, Serverless, Azure Service Bus, BizTalk Server, and Hybrid Integration

Pieter Vandenheede

Stories, tips & tricks for BizTalk Server, Azure, Data Science & Machine Learning

Glenn Colpaert

Blogging with my head in the (Hybrid) Cloud and my feet on premises! Azure / IoT / Integration

BizMunch

BizTalk blog by Knut Urke

Dragon's BizTalk Blog

A blog about BizTalk, and other integration tools.

The Deployment Bunny

OS Deployment, Virtualization, Microsoft based Infrastructure...

Integration Made Easy

Demystify Integration Development

%d bloggers like this: