If the DTA database is huge in size Dtasp_clean HMData stored procedure may take days to execute in BizTalk 2004

ISSUE

===================

If the DTA database is huge in size Dtasp_clean HMData stored procedure may take days to execute in BizTalk 2004

CAUSE

=========

Dtasp_cleanHMData uses delete From table command in BizTalk 2004 and it used truncate table command in Biztalk 2006.So if you have a huge DTA database(of the order of 70-80 GB) it may take several days to execute the stored procedure to clean the DTA database in Biztalk 2004 environment.

 

The dtasp_CleanHMData stored procedure in BizTalk 2004 is:

CREATE PROCEDURE [dbo].[dtasp_CleanHMData]
@nCleanStatus int = 1
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
IF ((@nCleanStatus & 0x1) > 0)
BEGIN
DELETE FROM dta_Adapter WITH (SERIALIZABLE)
DELETE FROM dta_CallChain WITH (SERIALIZABLE)
DELETE FROM dta_DebugTrace WITH (SERIALIZABLE)
DELETE FROM dta_DecryptionSubject WITH (SERIALIZABLE)
DELETE FROM dta_Host WITH (SERIALIZABLE)
DELETE FROM dta_MessageBox WITH (SERIALIZABLE)
DELETE FROM dta_MessageFields WITH (SERIALIZABLE)
DELETE FROM dta_MessageFieldValues WITH (SERIALIZABLE)
DELETE FROM dta_MessageInOutEvents WITH (SERIALIZABLE)
DELETE FROM dta_MessageInstances WITH (SERIALIZABLE)
DELETE FROM dta_PartyName WITH (SERIALIZABLE)
DELETE FROM dta_PortName WITH (SERIALIZABLE)
DELETE FROM dta_SchemaName WITH (SERIALIZABLE)
DELETE FROM dta_ServiceInstanceExceptions WITH (SERIALIZABLE)
DELETE FROM dta_ServiceInstances WITH (SERIALIZABLE)
DELETE FROM dta_SigningSubject WITH (SERIALIZABLE)
DELETE FROM dta_Url WITH (SERIALIZABLE)
END
IF ((@nCleanStatus & 0x2) > 0)
BEGIN
DELETE FROM TDDS_FailedTrackingData WITH (SERIALIZABLE)
END
IF ((@nCleanStatus & 0x4) > 0)
BEGIN
DELETE FROM dta_Services WITH (SERIALIZABLE) WHERE nServiceId > 13
END
COMMIT TRAN
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

 

GO

The same stored procedure in BizTalk 2006/ 2006 R2 is:

USE [BizTalkDTADb]

GO

/****** Object: StoredProcedure [dbo].[dtasp_CleanHMData] Script Date: 05/12/2009 14:57:07 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[dtasp_CleanHMData]

@nCleanStatus int = 1

AS

exec dtasp_DropViews

IF ((@nCleanStatus & 0x1) > 0)

BEGIN

      TRUNCATE TABLE dta_Adapter

      TRUNCATE TABLE dta_DecryptionSubject

      TRUNCATE TABLE dta_Host

      TRUNCATE TABLE dta_MessageBox

      TRUNCATE TABLE dta_MessageFields

      TRUNCATE TABLE dta_PartyName

      TRUNCATE TABLE dta_PortName

      TRUNCATE TABLE dta_SchemaName

      TRUNCATE TABLE dta_ServiceInstanceExceptions

      TRUNCATE TABLE dta_SigningSubject

      TRUNCATE TABLE dta_CallChain

      TRUNCATE TABLE dta_DebugTrace

      TRUNCATE TABLE dta_MessageFieldValues

      TRUNCATE TABLE dta_MessageInOutEvents

      TRUNCATE TABLE dta_ServiceInstances

      TRUNCATE TABLE Tracking_Fragments1

      TRUNCATE TABLE Tracking_Parts1

      TRUNCATE TABLE Tracking_Spool1

      TRUNCATE TABLE Tracking_Fragments2

      TRUNCATE TABLE Tracking_Parts2

      TRUNCATE TABLE Tracking_Spool2

END

IF ((@nCleanStatus & 0x2) > 0)

BEGIN

      TRUNCATE TABLE TDDS_FailedTrackingData

END

IF ((@nCleanStatus & 0x4) > 0)

BEGIN

      DELETE FROM dta_Services WITH (SERIALIZABLE) WHERE nServiceId > 13

END

if ((@nCleanStatus & 0x8) > 0)

BEGIN

      TRUNCATE TABLE Tracking_Fragments1

      TRUNCATE TABLE Tracking_Parts1

      TRUNCATE TABLE Tracking_Spool1

      TRUNCATE TABLE Tracking_Fragments2

      TRUNCATE TABLE Tracking_Parts2

      TRUNCATE TABLE Tracking_Spool2

END

exec dtasp_CreateMessageFactsFindMsgViews

exec dtasp_CreateRealNamesView

exec dtasp_CreateServiceFactsView

RESOLUTION

===========

Don’t use dtasp_CleanHMData in Biztalk 2004 environment. You can use the script Bts_tracking_shrinkexistingdatabase.sql from the KB 894253 to clean the DTA database as this script uses truncate Table command.