Geeks With Blogs
Bill Osuch - Random geek notes

I needed to write a quick job that would notify support folks whenever a BizTalk message was suspended. Yes, you could use WMI and/or MOM, but since we're not currently using anything along those lines this was the quickest way to go.

Run this against the BizTalkMsgDb database:

SELECT
      nvcName as ApplicationName,
      DATEADD(hh,-6,dtSuspendTimeStamp) as DateSuspended, -- Subtract the appropriate hours for your timezone
      nvcAdapter as Adapter,
      nvcURI as URI,
      nvcErrorDescription as ErrorDescription
FROM InstancesSuspended
LEFT OUTER JOIN [Services]
      on InstancesSuspended.uidServiceID = [Services].uidServiceID
LEFT OUTER JOIN Modules
      on Modules.nModuleID = [Services].nModuleID
WHERE DtCreated between DATEADD(dd,-7,GETUTCDATE()) and GETUTCDATE()
and nErrorCategory=0
ORDER BY dtCreated desc

Posted on Monday, November 29, 2010 8:47 PM BizTalk | Back to top


Comments on this post: Directly querying the BizTalk database for suspended messages

# re: Directly querying the BizTalk database for suspended messages
Requesting Gravatar...
Thanks for posting this, saved me tons of time and allowed quicker resolution of an issue. Appreciate!
Left by Marie Haggberg on Apr 27, 2011 12:45 PM

# re: Directly querying the BizTalk database for suspended messages
Requesting Gravatar...
querying the messagebox directly is less than recommended, but if you're going to do it please keep these notes in mind:

set nocount on
set transaction isolation level read comitted
set deadlock_priority low
always use the "with(nolock)" hint on all of your tables
don't ever insert, update, delete directly in the database! SELECT only!
Left by Will on Jan 25, 2012 10:04 AM

# re: Directly querying the BizTalk database for suspended messages
Requesting Gravatar...
Good post OP, very helpful.

Will, good tips but could you explain the purpose of having isolation level 'read committed' in conjunction with nolock hints in the same query?

Seems to me that is just telling your session to NOT allow dirty reads, and then specifying to ONLY use dirty reads on each table in the query.

Why not just use 'read uncommitted' without the hints?
Left by Josh on Dec 23, 2016 1:01 PM

# re: Directly querying the BizTalk database for suspended messages
Requesting Gravatar...
Excelent! thanks for sharing!
can you execute HAT's querys, without HAT?
Left by Javier on Sep 28, 2017 8:23 AM

Your comment:
 (will show your gravatar)


Copyright © Bill Osuch | Powered by: GeeksWithBlogs.net