I have been in a customer recently that had this rare alert in the SQL Server Log while running the TrackedMessages_Copy_BizTalkMsgBoxDb job (never seen before)
“Executed as user: USERNAME. Invalid object name ‘BizTalkDTADb.dbo.Tracking_Parts1’. [SQLSTATE 42S02] (Error 208). The step failed.”
At first glance i thought it was a security issue concerning the SQL Server Agent account rights within the database but it was not the case, since the SQL Server agent was well configured and has access to all databases in the instance.
I have spent some time investigating the issue, even i have ensured the real existence of the BizTalkDTADb.dbo.Tracking_Parts1 table. 🙂 everything seemed to be right but the job was not working at all. Having an interview with the BizTalk Administrator i found out they moved the Tracking Database to a different instance the day the alert started to throw. Right! Good Point! They followed the “How to Move the BizTalk Server Databases” online documentation. Checking the whole points we realized they forgot to update the TrackedMessages_Copy_BizTalkMsgBoxDb job to point to the new server.
How to fix it
Simply edit the Purge Step in the TrackedMessages_Copy_BizTalkMsgBoxDb job to point the new server name
exec bts_CopyTrackedMessagesToDTA ‘NewServerName’, ‘BizTalkDTADb’
and Good Luck! 🙂