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