Manually purge DTA

Hi everyone, on development and validation server, I frequently got space disk issue due to DTA log size.

So, if your developpers don’t want to configure all biztalk jobs, or you forget to configure those job on validation stage please follow the next instructions :

  • Stop all BizTalk Host Instance
  • Stop IIS Server
  • Stop SSO service
  • Connect to BizTalk SQL server with SQL Server Management Studio (Run ssms)
  • In Object explorer go to BizTalkDtaDb
  • Execute Stored procedure dtasp_PurgeAllCompletedTrackingData
  • After execution, I recommend to shrink the database
  • Now check the Database size, and you’ll see that it’s empty 🙂

 

For more information please go on http://msdn.microsoft.com/en-us/library/aa561918.aspx

Performance issue in Development plateform : one solution clean your BizTalk DB

My dev machine works so slowly, so I decided to clean it. I found a guide to perform it.

But don’t do it on production plateform, it won’t be supported

Here are few steps to clean up BizTalk Message Box Database.

1. There is a Stored Proc bts_CleanupMsgbox in BizTalkMsgBoxDb Database. By default this stored proc does nothing. It’s Dummy. This is to prevent accidental execution in the Production Server.

2. Execute a script to recreate bts_CleanupMsgbox. Script can be found at <BizTalk Dir>\Schema\msgbox_cleanup_logic.sql. This script should be executed in BizTalkMsgBoxDb Database.

3. Stop BizTalk services from the Services (Start -> Run -> Services.msc).

4. Reset IIS server incase if you have any running isolated host instances

5. Execute stored procedure bts_CleanupMsgbox on your message box database

This SP will take an input parameter @fLeaveActSubs.
Values for this should be ’0′ or ’1′. ’1′ is the default incase if you dont supply a value.

Setting the value to ’0′ results in clearing the active subscriptions and you may have to redeploy the application. Think twice before doing this.

6. The above will not clear off all the subscription but will mark for deletion . Execute the stored procedure bts_PurgeSubscriptions on your message box database to purge them all.

7. Start BizTalk services

To clean up the Tracking data in BizTalkDTADb Database

1. Stop BizTalk services from the Services (Start -> Run -> Services.msc).

2. Execute stored procedure dtasp_CleanHMData on BizTalkDTADb database.

3. Start the BizTalk services

source (http://masteringbiztalkserver.wordpress.com/tag/dtasp_cleanhmdata/)

(Message 4035) BACKUP LOG cannot be performed because there is no current database backup

Hi everyone, last time I got a disk space issue, after purging some data and backup, I tried to run the biztalk jobs. But the DTA backup job  failed with this error

Executed as user: xxx. Processed 3233 pages for database ‘BizTalkDTADb’, file ‘BizTalkDTADb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 3233 pages in 0.400 seconds (63.133 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 2 pages for database ‘BizTalkMgmtDb’, file ‘BizTalkMgmtDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 2 pages in 0.008 seconds (1.342 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 13829 pages for database ‘BizTalkMsgBoxDb’, file ‘BizTalkMsgBoxDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 13829 pages in 1.118 seconds (96.631 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 1 pages for database ‘BizTalkRuleEngineDb’, file ‘BizTalkRuleEngineDb_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG successfully processed 1 pages in 0.022 seconds (0.044 MB/sec). [SQLSTATE 01000] (Message 3014) Processed 1 pages for database ‘SSODB’, file ‘SSODB_log’ on file 1. [SQLSTATE 01000] (Message 4035) BACKUP LOG cannot be performed because there is no current database backup. [SQLSTATE 42000] (Error 4214) BACKUP LOG is terminating abnormally. [SQLSTATE 42000] (Error 3013) BACKUP LOG successfully processed 1 pages in 0.018 seconds (0.271 MB/sec). [SQLSTATE 01000] (Error 3014). The step failed.

To summarize, the job could not find the full backup which is the reference for one day (but  does exist on disks).

Fortunately, it appends on test environnement. So I decide to clear all Backup and force the job to create a new full one. Here is the process:

  • First delete all biztalk Backup
  • Connect Sql server management studio, to  the BizTalk management Database
  • Execute the stored procedure [dbo].[sp_ForceFullBackup]
  • Next re-run the job, a newly ful backup is created

This workaround is effective, but in production environnement, you must keep an eye on the backup disk and prevent any empty space to avoid this problem.

No instance in « track message service instance »

This is a problem that I saw recently. After a BizTalk Configuration step with domain account.

When you execute a BizTalk Application you can see the Active Service Instance, but after you can’t see any tracking data.

Three checks :

  • First one verify you have an host with tracking active
  • Second one check that activity tracking is enable on orchestration or port
  • Third one go on SQL server log and verify that you don’t have « access failed login » event, if you found it give the right g to read write to the DTA for the Host Instance with Tracking enable