Unable to view Tracking records
When attempting to query tracking data in the BizTalk Services Management Portal, I continually get errors and cannot retrieve the data.
The most common error returned says "An error occurred while retrieving tracking data An error occurred while processing this request".
The tracked data (storage and SQL) is not archived by default. Manual intervention is required to clean up the data.
If you are unable to get the tracking records on the Tracking portal, but the runtime environment is working fine, then most likely the tracking data has reached to a limit where it is causing the query performance issues.
The best way is to begin with purging the data as explained in the previous blog section - How to Manage Tracked Records
Even after cleaning the reasonable data, if you still continue to get error on the portal, check if you are able to query the SQL database directly.
You can use SSMS to connect to SQL Azure. To get details of the SQL Azure being used, you will need to go to Azure portal/click BizTalk Services. On the dashboard tab, you will see the SQL Azure Database being used for tracking. If you click the database name, it will take you to the database page. Click Dashboard. On the left you will see ‘Show connection string’. Click this and it will show you the database name and the user ID for the database.
Once connected, try running a simple query like Select * from TrackRecordProperties2. If this query is taking long time or not completing, it indicates you need to clean some more data.
For some of the customers’ the retention policy may add burden to the Tracking database or because of business volume even small number of days may contain a large amount of data. If data sizing guideline don’t go well with retention period, other option is to backup data to some other SQL server before purging from the tracking store till Tracking query performance is optimized.
Other option is to upgrade the SQL Azure server to higher edition / performance level which will provide more processing power to handle large tracking environments. The article below helps you understand move the database from Web/Business edition to New Service tiers.
https://azure.microsoft.com/en-us/documentation/articles/sql-database-upgrade-new-service-tiers/
Run the following query on the master database to retrieve the average DTU consumption for a database:
SELECT start_time, end_time
,(SELECT Max(v)
FROM (VALUES (avg_cpu_percent)
, (avg_physical_data_read_percent)
, (avg_log_write_percent)
) AS value(v)) AS [avg_DTU_percent]
FROM sys.resource_stats
WHERE database_name = '<your db name>'
ORDER BY end_time DESC;
Here is a table that provides a mapping of the Web/Business resource consumption percentage to equivalent new tier performance levels: