BizTalk SQL Database - Configuration Errors And Resolution

Introduction

This article is intended to be a knowledge base of BizTalk SQL database configuration Errors, Causes, and Resolutions, documented in all stages/components of BizTalk. If the database is not configured in the manner that Microsoft requires, then we will see these types of errors. Let’s check the cause and solution for these errors.

Error

The above screenshot and error is self-explanatory and the error says “SIMPLE (Def=FULL for BizTalk Dbs - Not compatible with BizTalk BackupJob)”, which means BizTalk DB is not compatible with BizTalk SQL jobs and we need to make it compatible. Microsoft says recovery model should be FULL for each of the databases in BizTalk server.

Cause

This is because recovery model is SIMPLE and it should be FULL. We need to check which of these databases are currently configured in SIMPLE recovery mode. That can be exposed by executing the following stored procedure against each BizTalk database. The information we want is in the ‘status’ column. Check the result of each stored procedure.

EXEC sp_helpdb 'BizTalkMgmtDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkMsgBoxDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkDTADb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BAMPrimaryImport'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkRuleEngineDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'SSODB'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
EXEC sp_helpdb 'BAMArchive'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault

Resolution

We need to change the recovery model to FULL and this will be make our BizTalk database compatible, per Microsoft. Run the below scripts to change the recovery mode from SIMPLE to FULL for each of the databases identified in the previous step:

ALTER DATABASE BizTalkMgmtDb
SET recovery FULL
ALTER DATABASE BizTalkMsgBoxDb
SET recovery FULL
ALTER DATABASE BizTalkDTADb
SET recovery FULL
ALTER DATABASE BAMPrimaryImport
SET recovery FULL
ALTER DATABASE BizTalkRuleEngineDb
SET recovery FULL
ALTER DATABASE SSODB
SET recovery FULL
ALTER DATABASE BAMArchive
SET recovery FULL

Verification

Now, again execute the following stored procedure against each BizTalk database. This time information will be different and that can be checked in the ‘status’ column,

EXEC sp_helpdb 'BizTalkMgmtDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkMsgBoxDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkDTADb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BAMPrimaryImport'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'BizTalkRuleEngineDb'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault
EXEC sp_helpdb 'SSODB'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
EXEC sp_helpdb 'BAMArchive'
Result:
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=782, Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52,
IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled, IsLocalCursorsDefault

Errors

We have two different errors here in this category,

Backup BizTalk Server SQL job is disabled or not running.
Monitor BizTalk Server SQL job also disabled or not running.

BizTalk Server SQL Agent jobs are very critical jobs that maintain the BizTalk databases and performance, and also control the growth of the database when it's running and configured properly. Both of the above jobs are important, and must be configured in order to be able to perform their roles.

Causes

The first error clearly says, either this job is disabled, and no history is found or maybe it's not running in the proper way. This job has 4 steps and we have to configure this properly to execute all steps. We’ll check in the resolution part how to configure this in the job properly.

The second error says, “This job failed probably because of some MsgBox DB integrity issues - Check please the integrity issues reported by this application”, that means there is some issue in MsgBox db that caused this issue. If we do more analysis, then we should have some error integrity issue in MsgBox db that causes this error. We have to fix this to run Monitor BizTalk Server job properly.

Resolution

The Backup BizTalk Server SQL Agent job is a critical job that must be configured in order to be able to successfully back up the BizTalk Server databases that participate in Distributed Transaction Coordinator transactions. Databases that participate in DTC transactions such as with BizTalk must be backed up and restored as a set to ensure consistency.

Refer

https://social.msdn.microsoft.com/Forums/en-US/d1fbdcc8-6b63-439f-942b-f4d3438432ef/backup-biztalk-server-job
https://docs.microsoft.com/en-us/biztalk/core/how-to-configure-the-backup-biztalk-server-job

We’ll connect to BizTalk database server and expend SQL Server Agent. All BizTalk server related SQL jobs will be listed there, and we could verify if ‘Backup BizTalk Server’ job enabled and running properly. As per below screenshot, this job is not enabled, and we have to configure it below enable.

We’ll go to the property of this job and check the schedule, this job runs every 15 minutes.

Now, we have to check all the steps under this job, we have 4 steps here and on success we'll go to the next step:

Set Compression Option
BackupFull
MarkandBackuplog
Clear Backup History

BizTalk SQL Database - Configuration Errors And Resolution

Let’s start with step 1 ‘Set Compression Option’, default command on this step would be, where we can set the parameter @bCompression value to 0 or 1. It depends if we want to use compression or not.

0 – Don’t use Compression
1 – Use Compression.

exec [dbo].[sp_SetBackupCompression] @bCompression = 0 /*0 - Do not use Compression, 1 - Use Compression */

We’ll modify our command and set the value to 1, now the update command will be,

exec [dbo].[sp_SetBackupCompression] @bCompression = 1 /*0 - Do not use Compression, 1 - Use Compression */

Click OK to save this change.

Move to the next step; i.e., step 2 ‘BackupFull’. In this step we’ll configure the frequency, name of server and destination path of the backup files.

Default command value of this step is,

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '<destination path>' /* location of backup files */

where ‘d’ indicates daily frequency, and we have to set the destination path to save the backup files.

We’ll modify our command and now the updated command will be,

exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, 'F:\Backup\BizTalk Database\Full' /* location of backup files */

Click OK to save this change.

Move to the next step; i.e. ,step 3 ‘MarkAndBackupLog’. In this step we’ll configure the destination path of log files.

Default command value of this step is,

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, '<destination path>' /* location of backup files */

and we have to set the destination path to save the log files.

We’ll modify our command and now the updated command will be,

exec [dbo].[sp_MarkAll] 'BTS' /* Log mark name */, 'F:\Backup\BizTalk Database\Logs' /* location of backup files */

Where we save the Log files on the given destination.

Click OK to save this change.

Move to the next and final step; i.e., step 4 ,‘Clear Backup History’. In this step we’ll delete the backup history. This step requires parameter @DaysToKeep which takes the value for how many days of data you want to keep in the history table.

A default command value of this step is,

exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14

@DaysToKeep: Default value is 14 days. This determines how long the backup history is kept in the Adm_BackupHistory table. Periodically clearing the backup history helps maintain the Adm_BackupHistory table to an appropriate size. And we have one more optional parameter @UseLocalTime: Tells the procedure to use local time. The default value is 0. It uses current UTC time – GETUTCDATE() – 2007-05-04 01:34:11.933. If set to 1, then it uses local time – GETDATE() – 2007-05-03 18:34:11.933

We’ll modify our command and now the updated command will be,

exec [dbo].[sp_DeleteBackupHistory] @DaysToKeep=14 , @UseLocalTime =1

Click OK to save this change.

Now everything is done, and all steps are configured properly.

Right-click the Backup BizTalk Server job, and select Enable. The status should change to Success. And we can see this in the below screenshot.

Verification

Once all steps are configured and the job is enabled then we can check the job execution result in job history. Simply go to the properties of the job and click on ‘view Job History’, it’ll open a new window where we can view all the step's result and execution time.

Error

When we run BHM or MsgBoxViewer then we will find these types of errors like, “DTA Orphaned Instances (Incompleted Instances in DTA but not in Msgbox)” or “Total Integrity issues Found in MsgBox Db(s)”. These errors come if we terminate an orphaned tracking instance not in the proper manner. So ideally, we should use Terminal tool to clear such types of instances.

Cause

When we purge data from the BizTalk Tracking (BizTalkDTADb) database, the DTA Purge and Archive job purge different types of tracking information such as message and service instance information, orchestration event information, and rules engine tracking data from the BizTalk Tracking (BizTalkDTADb) database.

But, if an exception is caught and handled in an orchestration without tracking turned on, an orphaned tracking instance with a Started state and exception information may be inserted into the BizTalk Tracking (BizTalkDTADb) database. This record will remain after purging the database.

Sometimes this can happen for a few, very common, reasons. For example, an orchestration might throw an exception, or might be terminated by an administrator. We can detect these using the following query,

SELECT COUNT(*) FROM [BizTalkDTAdb].[dbo].[dta_ServiceInstances]
WHERE dtEndTime IS NULL AND [uidServiceInstanceId] NOT IN
(SELECT [uidInstanceID] FROM [BizTalkMsgBoxDb].[dbo].[Instances] WITH (NOLOCK)
UNION
SELECT [StreamID] FROM [BizTalkMsgBoxDb].[dbo].[TrackingData] with (NOLOCK))

Resolution

The recommended way to remove these instances is through Terminator tool and these can also be detected by the MsgBoxViewer, a great tool that we’d recommend for all BizTalk administrators. Also we can use the below query to clear these data.

BEGIN TRAN
USE [biztalkDTADb]
UPDATE [dbo].[dta_ServiceInstances] SET [dtEndTime] = GetUTCDate() WHERE dtEndTime is NULL
AND [uidServiceInstanceId] NOT IN
(SELECT [uidInstanceID] FROM BizTalkMsgBoxDb.[dbo].[Instances] WITH (NOLOCK)
UNION
SELECT [StreamID] FROM BizTalkMsgBoxDb.[dbo].[TrackingData] WITH (NOLOCK))
-- If count match with above result then uncomment below query and run
-- Commit tran
-- If it does NOT match then uncomment and run below query
-- Rollback tran

Error

The error itself says, this is something like a bug “KB3062831 not installed on <BizTalkServer>: Master secret key cannot be restored and/or memory leaks can occur in SSO”. We noticed high memory usage by Enterprise Single Sign-On service and after running BHM report found this cause of the problem,

Cause

After applying the following hotfix from KB3000847, we may find the memory usage of SSO service keeps increasing and will not be released before restarting it. The issue was already addressed on the MSDN blog.

https://blogs.msdn.microsoft.com/apacbiztalk/2015/05/08/sso-service-memory-leak-after-applying-hotfix-of-kb3000847/

Resolution

Install the following hotfixes on the BizTalk server and restart the server after installation.

https://support.microsoft.com/en-us/help/3062831/entsso-experiences-a-memory-leak-after-you-apply-hotfix-3000847
https://support.microsoft.com/en-us/help/3000847/fix-enterprise-single-sign-on-triggers-event-10536-and-error-code-0x80