BizTalk: 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 would see these types of errors. Let’s check out the causes and solutions for these errors.

Error

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

Cause

This is because the 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 made compatible for our BizTalk database as 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

Again, execute the following stored procedure against each BizTalk database. This time, the information will be different and 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 in this category:

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

BizTalk Server SQL Agent jobs are very critical jobs that maintain the BizTalk databases and performance, and it also controls the growth of the database when 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 properly. This job has 4 steps, and we have to configure this properly to execute all steps after each. We’ll check in the resolution part how to configure this job properly.

The second error says, “This job failed probably because of some MsgBox DB integrity issues — please check the integrity issues reported by this application.” That means there are some issues in MsgBox DB that cause 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 to:

• 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 the ‘Backup BizTalk Server’ job is enabled and running properly. As per the below screenshot, this job is not enabled, and we have to configure it below to enable.

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

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

1. Set Compression Option
2. BackupFull
3. MarkandBackuplog
4. Clear Backup History

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, and now 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 next step i.e. step 2 ‘BackupFull’, in this step we’ll configure the frequency, name of server and destination path of backup files.

The default command value of this step is:

 exec [dbo].[sp_BackupAllFull_Schedule] 'd' /* Frequency */, 'BTS' /* Name */, '' /* 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 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 step 3 ‘MarkAndBackupLog.’ In this step, we’ll configure the destination path of log files.

The default command value of this step is:

 exec [dbo].[sp_MarkAll] 'BTS' /*  Log mark name */, '' /* location of backup files */ and we have to set the destination path to save the log files.

We’ll modify our command and the updated command will be:

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

Save the Log files on the given destination.

Click OK to save this change.

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

The default command value of this step is:

 exec [dbo].[sp_DeleteBackupHistory] @ 

 @DaysToKeep: Default value is 14 days. 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 updated command will be:

 exec [dbo].[sp_DeleteBackupHistory] @ , @UseLocalTime 

Click OK to save this change.

Now everything is done, and all the steps configured properly. The final step is to enable this job and check if this is enabled without any issue.

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 the steps are configured and the job is enabled, we can check the job execution result in job history. Simply go to properties of job and click on ‘view Job History.’ It’ll open a new window where we can view the all step results and execution time.

Error

When we run BHM or MsgBoxViewer, we would find these types of errors: “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 purges 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 the 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 due to a few very common reasons.  For example, an orchestration might throw an exception or 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 the 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 the 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