BizTalk SQL Database – Configuration Errors and Resolution
Table of Contents
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.
BizTalk SQL Database – Configuration Errors and Resolution
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, IsLocalCursorsDefaultEXEC 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, IsLocalCursorsDefaultEXEC 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, IsLocalCursorsDefaultEXEC 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, IsLocalCursorsDefaultEXEC 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, IsLocalCursorsDefaultEXEC 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, IsFullTextEnabledEXEC 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, IsLocalCursorsDefaultResolution
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 BizTalkMgmtDbSET recovery FULLALTER DATABASE BizTalkMsgBoxDbSET recovery FULLALTER DATABASE BizTalkDTADbSET recovery FULLALTER DATABASE BAMPrimaryImportSET recovery FULLALTER DATABASE BizTalkRuleEngineDbSET recovery FULLALTER DATABASE SSODBSET recovery FULLALTER DATABASE BAMArchiveSET recovery FULLVerification
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, IsLocalCursorsDefaultEXEC 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, IsLocalCursorsDefaultEXEC 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, IsLocalCursorsDefaultEXEC 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, IsLocalCursorsDefaultEXEC 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, IsLocalCursorsDefaultEXEC 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, IsFullTextEnabledEXEC 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, IsLocalCursorsDefaultError
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, 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
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 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.
- Set Compression Option
- BackupFull
- MarkandBackuplog
- 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:BackupBizTalk DatabaseFull' /* 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:BackupBizTalk DatabaseLogs' /* 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.
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 TRANUSE [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 tranError
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.