After applying Biztalk SP2 you may receive the following error
Event Type: Error
Event Source: BAM EventBus Service
Event Category: None
Event ID: 6
Date: 13/01/2009
Time: 17:12:55
User: N/A
Computer: JDW54
Description:
Execute event error. Error(s) occurred while executing events, see TDDS_FailedTrackingData table for more details. SQLServer: SQL1, Database: BizTalkDTADb
You return the data from TDDS_FailedTrackingData and receive
535 SERVER1BizTalkMsgBoxDb1 {00000000-0000-0000-0000-000000000000} 13/01/2009 17:11:42 1 TDDS failed to execute event. Stored Procedure dtasp_ServiceEnd failed to run.Transaction (Process ID 95) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. <Binary>
536 SERVER1BizTalkMsgBoxDb1 {00000000-0000-0000-0000-000000000000} 13/01/2009 17:11:43 1 TDDS failed to execute event. Stored Procedure dtasp_ServiceEnd failed to run.Transaction (Process ID 83) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. <Binary>
When using HAT and the query pages (messages received in the last day) you notice that some items are logged and others are not, you also notice that running the query pages can also take some time to execute or timeout.
First step I would recommend would to download MessageBoxViewer http://blogs.technet.com/jpierauc/pages/msgboxviewer.aspx. Select all of the tests and then collect all the information from the server. Once executed scroll to the bottom of the results and see what critical errors have been raised. Maybe you will see the following:
• BizTalk Job ‘DTA Purge and Archive’ is renamed or missing
• BizTalk Job ‘Backup BizTalk Server’ was executed with errors
• BizTalk Job ‘MessageBox_Message_ManageRefCountLog_BizTalkMsgBoxDb’ executed with errors
• BizTalk SVC Control message is missing
If you do have these errors confirm that they have not been renamed if they haven’t it plausible that even though BizTalk reports that it is running SP2, the BTS DB Schema’s have not been updated
If this is the case navigate to the following folder
C:Documents and SettingsAll UsersApplication DataMicrosoftE-Business Servers UpdatesBTS2004SP2
Then run:
BTS_Tracking_ShrinkExistingDatabase.sql
DTA_Schema_Upgrade.sql
Mgmt_Schema_Upgrade.sql
MsgBox_Schema_Upgrade.sql
updateEDI.sql
If after running the upgrade scripts Message Box viewer still shows a critical error ie control message is still missing. You will need to Uninstall SP2 roll back the DB’s to SP1 and then rerun the upgrade
Helpful scripts
PRINT 'Top 1 FROM TDDS_FailedTrackingData'
-- truncate table TDDS_FailedTrackingData
SELECT top 1 *
FROM TDDS_FailedTrackingData (nolock)
ORDER BY dtLogTime DESC
PRINT 'HAT Sent'
-- Copyright (c) Microsoft Corporation. All rights reserved.
--
-- THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
-- WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
-- WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
-- THE ENTIRE RISK OF USE OR RESULTS IN CONNECTION WITH THE USE OF THIS CODE
-- AND INFORMATION REMAINS WITH THE USER.
-- Retreive all msgs sent in the past 24 hours --
-- Note: Joins are used for localized strings only.
-- This query uses heuristic and may provide imprecise numbers in some cases (e.g. when pass-through pipelines are used).
-- This query excludes internal messages and counts external messages only
-- Dates are retrieved using the local workstation time
-- In case the client is in a different timezone from the server
-- we extract the utc offset that is calculated for each client
declare @Timestamp as datetime
set @Timestamp = GETUTCDATE() - 2 -- local time converted to UTC
declare @Receive as nvarchar (100), @Send as nvarchar(100)
SELECT @Receive = strStatus FROM [dbo].[dta_MessageStatus] WHERE nMessageStatusId=0 -- Receive=0
SELECT @Send = strStatus FROM [dbo].[dta_MessageStatus] WHERE nMessageStatusId=1 -- Send=1
SELECT
[MessageInstance/SchemaName],
[Event/Direction],
dateadd(minute, 1, [Event/Timestamp]) as [Timestamp], -- can't use 'as [Event/Timestamp]' since this prevents SQL from using index on that column (conflicts with ORDER BY)
[Event/Adapter],
[Event/URL],
[Event/DecryptionCertificate],
[Event/Signature],
[ServiceInstance/InstanceID],
[ServiceInstance/ActivityID],
[MessageInstance/InstanceID],
[Event/EventID],
[MessageInstance/PartCount],
[MessageInstance/Size],
[Event/Party],
[Event/Port]
FROM dbo.dtav_MessageFacts mf WITH (READPAST)
WHERE [Event/Direction] = @Send
AND [Event/Timestamp] > @Timestamp
AND [MessageInstance/InstanceID] in
(
SELECT mioe.uidMessageInstanceId
FROM dbo.dta_MessageInOutEvents mioe WITH (READPAST)
GROUP BY mioe.uidMessageInstanceId
HAVING count (distinct cast (mioe.uidActivityId as varchar(36))) = 1 -- message participated in single activity only
AND min((mioe.nStatus-1)*(mioe.nStatus-1)) = 0 -- at least one send (nStatus=1)
AND min(mioe.dtTimestamp) > @Timestamp
)
ORDER BY [Event/Timestamp] desc
PRINT 'HAT Recieved'
-- Copyright (c) Microsoft Corporation. All rights reserved.
--
-- THIS CODE AND INFORMATION IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND,
-- WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
-- WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
-- THE ENTIRE RISK OF USE OR RESULTS IN CONNECTION WITH THE USE OF THIS CODE
-- AND INFORMATION REMAINS WITH THE USER.
-- Retreive all unparsed interchange msgs received in the past 24 hours --
-- Unparsed interchange is the wire level message --
-- Note: Joins are used for localized strings only
-- Dates are retrieved using the local workstation time
-- In case the client is in a different timezone from the server
-- we extract the utc offset that is calculated for each client
DECLARE @Timestamp1 as DATETIME
DECLARE @schemaId as int
SELECT @schemaId=nSchemaId FROM dbo.dta_SchemaName WHERE strSchemaName = N'Unparsed Interchange'
-- Time period is 24 hours
set @Timestamp1 = GETUTCDATE() - 2 -- local time converted to UTC
SELECT
[MessageInstance/SchemaName],
[Event/Direction],
dateadd(minute, 1, [Event/Timestamp]) as [Timestamp], -- can't use 'as [Event/Timestamp]' since this prevents SQL from using index on that column (conflicts with ORDER BY)
[Event/Adapter],
[Event/URL],
[Event/DecryptionCertificate],
[Event/Signature],
[ServiceInstance/InstanceID],
[ServiceInstance/ActivityID],
[MessageInstance/InstanceID],
[Event/EventID],
[MessageInstance/PartCount],
[MessageInstance/Size],
[Event/Party],
[Event/Port]
FROM [dbo].[dtav_MessageFacts] AS mf WITH (READPAST)
-- Joins used to retrieve localized strings --
JOIN [dbo].[dta_SchemaName] AS sn WITH (READPAST) ON sn.strSchemaName = mf.[MessageInstance/SchemaName]
JOIN [dbo].[dta_MessageStatus] st WITH (READPAST) ON st.strStatus = mf.[Event/Direction]
WHERE
-- Receive=0 --
st.nMessageStatusId = 0
AND
[Event/Timestamp] > @Timestamp1
AND
-- Unparsed Interchange is the original message received --
sn.nSchemaId = @schemaId
ORDER BY [Event/Timestamp] desc
/*
PRINT 'All TDDS_FailedTrackingData'
SELECT *
FROM TDDS_FailedTrackingData (nolock)
ORDER BY dtLogTime DESC
*/
—————————————————–
This blog is provided as is.
It is assumed that you have adequate recovery processes in place before carrying out any changes detailed in this blog.
You assume all risk for your use.
—————————————————–