BizTalk Best Practices Analyzer


One of my High Performance Learning Environment post reader gave me a hint to have a look at BizTalk Best Practices Analyzer. In past I have used this tool and I forgot that it could be useful to apply on the environment I created. The BizTalk Server 2006 Best Practices Analyzer (version V1.1)performs configuration-level verification by reading and reporting only. The Best Practices Analyzer gathers data from different information sources, such as Windows Management Instrumentation (WMI) classes, SQL Server databases, and registry entries. The Best Practices Analyzer uses the data to evaluate the deployment configuration. The Best Practices Analyzer does not modify any system settings, and is not a self-tuning tool. I downloaded it and gave it a spin inside my environment.

It came up with some critical issues; which were resolved by enabling first of all SQL Server agent (so jobs can run) and enable jobs. Second thing was to change steps inside the job ‘Backup BizTalk Server’ because of the following error:

BackupDiskFile::CreateMedia: Backup device ‘C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLBackupHPLBIZTALK_BizTalkDTADb_Full_BTS_2008_07_10_11_10_40_157.bak’ failed to create. Operating system error 123(The filename, directory name, or volume label syntax is incorrect.).

I fixed this by change two steps called BackupFull and MarkAndBackupLog, both containing TSQL Script commands.

exec [dbo].[sp_BackupAllFull_Schedule] ‘d’ /* Frequency */, ‘BTS’ /* Name */, ‘‘ /* location of backup files */


exec [dbo].[sp_BackupAllFull_Schedule] ‘d’ /* Frequency */, ‘BTS’ /* Name */, ‘BizTalkDTADb_Bak’ /* location of backup files */

exec [dbo].[sp_MarkAll] ‘BTS’ /* Log mark name */, ‘‘ /* location of backup files */


exec [dbo].[sp_MarkAll] ‘BTS’ /* Log mark name */, ‘‘ /* location of backup files */

Correcting this job and running the others resulted in only one critical error:
Error DTA Purge and Archive Job on Server HPLBIZTALK Did Not Succeeded In Last Run The SQL job DTA Purge and Archive (BizTalkDTADb) need to run successfully on your DTA database server HPLBIZTALK. Contact your SQL administrator to troubleshoot the problem.Do not rename this SQL job as this is not supported.

So I looked at TSQL Command:

exec dtasp_BackupAndPurgeTrackingDatabase
0, –@nLiveHours tinyint, –Any completed instance older than the live hours +live days
1, –@nLiveDays tinyint = 0, –will be deleted along with all associated data
30, –@nHardDeleteDays tinyint = 0, –all data older than this will be deleted.
null, –@nvcFolder nvarchar(1024) = null, –folder for backup files
null, –@nvcValidatingServer sysname = null,
0 –@fForceBackup int = 0 –

Googled around and I tackled this problem through a post by Saravana Kumar, where he states to change the above script into:

declare @dtLastBackup datetime set @dtLastBackup = GetUTCDate() exec dtasp_PurgeTrackingDatabase 1, 0, 1, @dtLastBackup

And yes this job works too, so now I am rid of all my critical issues. I must say using this tool is very useful after installation and configuration of your BizTalk environment. This should definitely be a step during your rollout of BizTalk inside an organization.


Author: Steef-Jan Wiggers

Steef-Jan Wiggers is all in on Microsoft Azure, Integration, and Data Science. He has over 15 years’ experience in a wide variety of scenarios such as custom .NET solution development, overseeing large enterprise integrations, building web services, managing projects, designing web services, experimenting with data, SQL Server database administration, and consulting. Steef-Jan loves challenges in the Microsoft playing field combining it with his domain knowledge in energy, utility, banking, insurance, healthcare, agriculture, (local) government, bio-sciences, retail, travel, and logistics. He is very active in the community as a blogger, TechNet Wiki author, book author, and global public speaker. For these efforts, Microsoft has recognized him a Microsoft MVP for the past 8 years.


Back to Top