Management Console Timing Out

 Uncategorized  Comments Off on Management Console Timing Out
Apr 262012

Have you ever needed to query through the BizTalk Management Console and find some messages that are tracked, but the console keeps timing out?

Here is a query that you can run:

use [BizTalkDTADb] GO set DEADLOCK_PRIORITY LOW SELECT TOP 1000 ms.strStatus AS [Event Type], svcs.strServiceName AS [Service Name], tr.strAdapter AS [Adapter], mioe.strUrl AS [URI], sn.strSchemaName AS [Schema Name], po.strPortName AS [Port Name], DATEADD(MI, DATEDIFF(MI,GETUTCDATE(),GETDATE()), mioe.dtTimestamp) AS [Timestamp], mioe.nPartCount AS [Part Count], mioe.nMessageSize AS [Size], ds.strDecryptionSubject AS [Decryption Certificate], ss.strSigningSubject AS [Signature], pn.strPartyName AS [Party Name], mioe.uidActivityId AS [Activity ID], mioe.uidMessageInstanceId AS [Message Instance ID], mioe.uidServiceInstanceId AS [Service Instance ID], si.uidServiceClassId AS [Service Class ID], svcs.strServiceType AS [Service Class] FROM dta_SchemaName sn JOIN [dbo].[dta_MessageInOutEvents] mioe ON mioe.nSchemaId = sn.nSchemaId LEFT JOIN [dbo].[dta_Adapter] tr ON tr.nAdapterId = mioe.nAdapterId LEFT JOIN [dbo].[dta_MessageStatus] ms ON mioe.nStatus = ms.nMessageStatusId LEFT JOIN [dbo].[dta_DecryptionSubject] ds ON ds.nDecryptionSubjectId = mioe.nDecryptionSubjectId LEFT JOIN [dbo].[dta_SigningSubject] ss ON ss.nSigningSubjectId = mioe.nSigningSubjectId LEFT JOIN [dbo].[dta_PartyName] pn ON pn.nPartyId = mioe.nPartyId LEFT JOIN [dbo].[dta_ServiceInstances] si ON mioe.uidServiceInstanceId = si.uidServiceInstanceId AND mioe.uidActivityId = si.uidActivityId LEFT JOIN [dbo].[dta_Services] svcs ON si.uidServiceId = svcs.uidServiceId LEFT JOIN [dbo].[dta_PortName] po ON po.nPortId = mioe.nPortId --Put the port name that you want to look at here: WHERE CAST(po.strPortName AS nvarchar(4000)) = 'Meditech Port' ORDER BY mioe.dtTimestamp DESC

Did I tell you that you should not be doing this? YOU SHOULDN’T!!!!!