Geeks With Blogs
Simon Emmanuel RIVAS

After syspreping a new BizTalk 2013 Box, I got into a tracking issue: when querying for tracked service instances or tracked message events, I got the frustrating message: “There are no items to show in this view”.

Before I go any further, I would like to underline that even though what follows happened on a BizTalk 2013 server after a sysprep (turned out the UpdateDatabase.vbs script didn't do its job properly), I think it could happen in other occasions : with other editions of BizTalk (almost sure it would happen on all editions ranging from 2006 to 2013, before that I have too little experience), when you move the BizTalk databases on another server (including in the case of BizTalk Log Shipping)…

So I checked that tracking was set for the BizTalk artifacts I wanted to track and that I had a tracking host running, and it was OK.

Then I checked the event log and the tracking job history, and both told me that something was wrong.

Checks: Event Viewer

The event viewer showed the following errors:

clip_image001

The error description was:

Either another TDDS is processing the same data or there is an orphaned session in SQL server holding TDDS lock.A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server) SQLServer: BIZTALK2013, Database: BizTalkMsgBoxDb

Here I understood the source of the problem: BIZTALK2013 was the former name of the server (before sysprep). After sysprep, its name was DEMODEM-MO1DP4E.

Checks: Tracking Job History

So I continued my checks and looked at the TrackedMessages_Copy_BizTalkMsgBoxDb job.

clip_image001[6]

The error description was:

Could not find server 'BIZTALK2013' in sys.servers. Verify that the correct server name was specified. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys.servers. [SQLSTATE 42000] (Error 7202) An error occurred while inserting data in the Tracking_Parts1 table [SQLSTATE 42000] (Error 50000). The step failed.

So same problem as in the event viewer.

Checks: Message Box Database

Last but not least: I wanted to check the content of two tables of the Message box DB :

SELECT count(uidMessageID )

FROM dbo.TrackingMessageReferences

 

SELECT COUNT(*) FROM [BizTalkMsgBoxDb].[dbo].[TrackingData]

clip_image001[8]

Why precisely those two tables? In my experience, the dbo.TrackingMessageReferences table is purged by the tracking job. So the fact that rows are present in this table indicate that the configuration of the job is incorrect (that only confirms what was seen earlier in the job history).

The dbo.TrackingData table is purged by the tracking host(s). The fact that rows are present in this table indicate that the TDDS configuration is incorrect, and that also confirms what was seen earlier in the event viewer.

Fixing the tracking job

So let’s start with the easiest one: the tracking job.

In the SQL Server Agent, I edited the TrackedMessages_Copy_BizTalkMsgBoxDb job and then edited the Purge step:

clip_image001[10]

You can manually update the job step, but if you don’t want to (for whatever reason), there is a stored procedure for that: [BizTalkMsgBoxDb].[dbo].[bts_UpdateCopyTrackedMessagesJob]

NOTE: I also found a reference to the old server name in the Operations_OperateOnInstances_OnMaster_BizTalkMsgBoxDb job.

So after executing this procedure with the correct server name, the job completed successfully:

clip_image001[12]

And running the previous query confirmed that the job now did its… well, job J

clip_image002

So one problem down, one to go.

Fixing the TDDS configuration

TDDS configuration (used by the tracking hosts to move events from the message box DB to the tracking DB) can be found in the Message Box DB in 2 tables :

  • TDDS_Destinations
  • TDDS_Sources

Querying those two tables with the following query

SELECT [DestinationID]

      ,[DestinationName]

      ,[ConnectionString]

  FROM [BizTalkMgmtDb].[dbo].[TDDS_Destinations]

 

SELECT [SourceID]

      ,[DestinationID]

      ,[SourceName]

      ,[ConnectionString]

      ,[StreamType]

      ,[AcceptableLatency]

      ,[Enabled]

  FROM [BizTalkMgmtDb].[dbo].[TDDS_Sources]

Gave the following results :

clip_image002

There is a stored procedure for updating the destinations ([BizTalkMgmtDb].[dbo].[trk_UpdateTDDS]), but I couldn’t find one for updating the sources. So I suggest you “manually” update the connection strings in both tables. I don’t know if the SourceName values are of any importance, but in order to be absolutely clean I also updated those.

After doing so, tadaaa! (note: you may have to restart the tracking host(s), not sure though…)

clip_image003

And this time records were found when querying the DTA Db with the admin console:

clip_image004

I hope this will help someone! Cheers

Posted on Thursday, November 14, 2013 12:41 PM BizTalk , Tracking , TDDS , Tracking job | Back to top


Comments on this post: No tracking data in the BizTalk Admin Console : no items to show in this view

# re: No tracking data in the BizTalk Admin Console : no items to show in this view
Requesting Gravatar...
Thanks for posting the knowledge ... helped me :-)
Left by SteveC on Jul 09, 2014 1:03 PM

# re: No tracking data in the BizTalk Admin Console : no items to show in this view
Requesting Gravatar...
You're welcome! :)
Left by Simon on Jul 11, 2014 9:48 AM

# re: No tracking data in the BizTalk Admin Console : no items to show in this view
Requesting Gravatar...
Hello Simon,
I have been facing the issue of not seeing tracked message events from the last couple of days in our dev server. Global tracking option is set to 1 in the adm_Group table and all the BizTalk artifacts have the tracking options tick marked. Upon quering the [BizTalkMsgBoxDb].[dbo].[TrackingData] table, I see more than 24000 rows here.
But when I query the [BizTalkMgmtDb].[dbo].[TDDS_Sources] and [BizTalkMgmtDb].[dbo].[TDDS_Destinations] tables, I dont see anything wrong with the connection strings except that in the destinations table, I see one entry for BizTalkDTADb and one for BAMPrimaryImport, which different from the screen shot in the above article which has both the entries pointing to BizTalkDTADb.
Can you think of any other possible reasons for tracked message events not appearing in the Admin Console. Appreciate if you can suggest some ideas.

Thanks
Left by Rajiv on Feb 13, 2015 12:17 AM

# re: No tracking data in the BizTalk Admin Console : no items to show in this view
Requesting Gravatar...
Hello Rajiv,
What you see in the TDDS_Destinations table seems OK. I have had a similar experience lately with a BizTalk 2013 dev server. Have you tried restarting the tracking host, SQL Agent, SQL Server, the WMI service etc ? I can't remember exactly which one solved the issue for me...
Left by Simon on Feb 13, 2015 7:15 PM

# re: No tracking data in the BizTalk Admin Console : no items to show in this view
Requesting Gravatar...
Hi,

We have been getting BAMEventBus warning as : Reading error. Exception information: TDDS failed to read from source database. SQLServer: ######, Database: BizTalkMsgBoxDb.Culture is not supported. Parameter name: culture 66577 (0x10411) is an invalid culture identifier..

Have tried everything related to checking configurations, using message box viewer, BizTalk Health monitor but not getting what the issue is.

We are running on BizTalk Server 2010 and using server collation as : Japanese_Unicode_CI_AS_KS_WS.

Please help!
Left by Deepika on Mar 23, 2016 8:24 AM

# re: No tracking data in the BizTalk Admin Console : no items to show in this view
Requesting Gravatar...
Hello Deepika,

Can you please execute the following query against the MgmtDb :
SELECT [ProductLanguage]
FROM [BizTalkMgmtDb].[dbo].[BizTalkDBVersion]

I guess the result will be 66577 ?
Would you mind changing it to 1041 (which should stand for japanese according to this source : https://msdn.microsoft.com/en-us/library/ee825488(v=cs.20).aspx) and then restarting all host instances?

Simon
Left by Simon on Mar 25, 2016 1:09 PM

# re: No tracking data in the BizTalk Admin Console : no items to show in this view
Requesting Gravatar...
Hi Simon,

Thanks for your inputs!
For this query, I am getting the output as 1033 [en-US], which is the same as on the old BizTalk Servers [2006 version] and there tracking is happening properly.

Do you still want me to change it to 1041? And is the change required only for BizTalkMgmt DB level and not on the entire instance?

Thanks!
Deepika
Left by Deepika on Mar 28, 2016 7:53 AM

Your comment:
 (will show your gravatar)


Copyright © S.E.R. | Powered by: GeeksWithBlogs.net