Share via

BizTalk Troubleshooting: SQL Database Configuration Errors

  • Article

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 database not configured in the manner that Microsoft requires, then we would see these types of errors. Let’s check the cause and solution for these errors. 

↑ Back to top

Error

Above screen shot and error it self-explanatory and error says "SIMPLE (Def=FULL 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, recovery model should be FULL for each of the databases in BizTalk server.

Cause

This is because of 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 compatible 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

Now again executing the following stored procedure against each BizTalk database. This time information will be different and that can be check 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

↑ Back to top

Error

We have two different errors here in this category, 

  1. Backup BizTalk Server SQL job is disabled or not running 
  2. 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, it also controls the growth of database when running and configured properly. Above both jobs are important, must be configured in order to be able to perform their roles.

Causes

First error clearly says, either this job is disabled, and no history found or may be not running in proper way. This job has 4 steps and we have to configure this properly to execute all steps after each. We’ll check in resolution part how to configure this in job properly.
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 are some issue 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*: 

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 screen shot, 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 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, 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.

Default command value of this step is,

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

where 'd' indicate daily frequency, and we have to set the destination path to save the backup files.

We’ll modify our command and now 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 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 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 given destination.

Click OK to save this change.

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

Default command value of this step is,

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

@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] @DaysToKeep=14 , @UseLocalTime =1

Click OK to save this change.

Now everything done, and all steps configured properly. Final steps to enable this job and check if this 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 below screen shot.


Verification

Once all steps configured and job enabled then 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 new window where we can view the all step result and execution time.

↑ Back to top

Error

When we run BHM or MsgBoxViewer then we would find these type error 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 proper manner. So ideally, we should use Terminal tool to clear such type 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 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.
Sometime 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

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 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

↑ Back to top

Error


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 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. Issue were already address on 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 BizTalk server and restart the server after installation. 

↑ Back to top

See Also

Read suggested related topics:

Another important place to find a huge amount of BizTalk related articles is the TechNet Wiki itself. The best entry point is BizTalk Server Resources on the TechNet Wiki.

↑ Back to top