note: this will remove everything and should be tested in a UAT environment first.
If need to purge your messages in BizTalk 2004 that can’t be removed from HAT run through the following:
-- Stop Receive Locations
-- Stop BizTalk Service (all Nodes)
-- Make Sure HAT is cleared of all messages (apart from the stuck messages)
-- Drop BTS views
use biztalkdtadb exec dtasp_DropViews
GO
-- Truncate tables dta
use biztalkdtadb TRUNCATE TABLE dta_ServiceInstances
use biztalkdtadb TRUNCATE TABLE dta_MessageInstances
use biztalkdtadb TRUNCATE TABLE dta_MessageFieldValues
use biztalkdtadb TRUNCATE TABLE dta_CallChain
use biztalkdtadb TRUNCATE TABLE dta_MessageInOutEvents
use biztalkdtadb TRUNCATE TABLE dta_DebugTrace
use biztalkdtadb TRUNCATE TABLE dta_RulesAgendaUpdates
use biztalkdtadb TRUNCATE TABLE dta_RulesConditionEvaluation
use biztalkdtadb TRUNCATE TABLE dta_RulesFactActivity
use biztalkdtadb TRUNCATE TABLE dta_RulesFired
use biztalkdtadb TRUNCATE TABLE Tracking_Spool1
use biztalkdtadb TRUNCATE TABLE Tracking_Parts1
use biztalkdtadb TRUNCATE TABLE Tracking_Fragments1
use biztalkdtadb TRUNCATE TABLE Tracking_Spool2
use biztalkdtadb TRUNCATE TABLE Tracking_Parts2
use biztalkdtadb TRUNCATE TABLE Tracking_Fragments2
use biztalkdtadb TRUNCATE TABLE dta_serviceinstanceexceptions
GO
use biztalkdtadb exec sp_updatestats
GO
-- Recreate the views
use biztalkdtadb exec dtasp_CreateMessageFactsFindMsgViews
use biztalkdtadb exec dtasp_CreateRealNamesView
use biztalkdtadb exec dtasp_CreateServiceFactsView
GO
-- Truncate MsgBox
use biztalkMsgBoxdb exec bts_cleanupmsgbox
use biztalkmsgboxdb exec sp_updatestats
GO
-- shrink log
BACKUP LOG biztalkdtadb WITH TRUNCATE_ONLY
BACKUP LOG biztalkmsgboxdb WITH TRUNCATE_ONLY
GO
dbcc shrinkdatabase (BizTalkDTADb, 10)
dbcc shrinkfile (BizTalkDTADb_log, 10)
GO
dbcc shrinkdatabase (BizTalkmsgboxDb, 10)
dbcc shrinkfile (BizTalkmsgboxDb_log, 10)
GO
-- Confirm that the tables are empty
/*
use biztalkdtadb select * from dta_ServiceInstances
use biztalkdtadb select * from dta_serviceinstanceexceptions
use biztalkdtadb select * from dta_CallChain
use biztalkdtadb select * from dta_MessageInstances
use biztalkdtadb select * from dta_MessageFieldValues
use biztalkdtadb select * from dta_MessageInOutEvents
use biztalkdtadb select * from dta_DebugTrace
use biztalkdtadb select * from dta_RulesAgendaUpdates
use biztalkdtadb select * from dta_RulesConditionEvaluation
use biztalkdtadb select * from dta_RulesFactActivity
use biztalkdtadb select * from dta_RulesFired
use biztalkdtadb select * from Tracking_Spool1
use biztalkdtadb select * from Tracking_Parts1
use biztalkdtadb select * from Tracking_Fragments1
use biztalkdtadb select * from Tracking_Spool2
use biztalkdtadb select * from Tracking_Parts2
use biztalkdtadb select * from Tracking_Fragments2
use biztalkMsgBoxdb select * FROM Spool
use biztalkMsgBoxdb select * FROM MessageParts
use biztalkMsgBoxdb select * FROM MessageZeroSum
use biztalkMsgBoxdb select * FROM Tracking_spool1
use biztalkMsgBoxdb select * FROM Tracking_spool2
use biztalkMsgBoxdb select * FROM Tracking_parts1
use biztalkMsgBoxdb select * FROM Tracking_parts2
use biztalkMsgBoxdb select * FROM Tracking_fragments1
use biztalkMsgBoxdb select * FROM Tracking_fragments2
use biztalkMsgBoxdb select * FROM trackingdata
use biztalkMsgBoxdb select * FROM messagerefcountlogtotals
use biztalkMsgBoxdb select * FROM partrefcountlogtotals
use biztalkMsgBoxdb select * FROM instances
use biztalkMsgBoxdb select * FROM instancespendingoperations
use biztalkMsgBoxdb select * FROM instancestatemessagereferences_biztalkserverapplication
*/
-- Open HAT and remove any other messages
-- Start Biztalk on one node
-- Enable 1 recieve location and confirm that the message goes through
-- Start all Reclocations and confirm that the message goes through
-- Start all other BTS nodes
/*
–/ script to create bts_cleanupmsgbox if it does exist (biztalk 2004 sp1 or above)
–/ script can be found in Microsoft Biztalk Program FilesMicrosoft BizTalk Server 2004Schemamsgbox_cleanup_logic.sql
–/ Copyright (c) Microsoft Corporation. All rights reserved.
–/
–/ THIS CODE AND INFORMATION IS PROVIDED “AS IS” WITHOUT WARRANTY OF ANY KIND,
–/ WHETHER EXPRESSED OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE IMPLIED
–/ WARRANTIES OF MERCHANTABILITY AND/OR FITNESS FOR A PARTICULAR PURPOSE.
–/ THE ENTIRE RISK OF USE OR RESULTS IN CONNECTION WITH THE USE OF THIS CODE
–/ AND INFORMATION REMAINS WITH THE USER.
–/
——————————————————————————
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[bts_CleanupMsgbox]’) and OBJECTPROPERTY(id, N’IsProcedure’) = 1)
drop procedure [dbo].[bts_CleanupMsgbox]
GO
CREATE PROCEDURE [dbo].[bts_CleanupMsgbox]
@fLeaveActSubs int = 1
AS
set nocount on
set transaction isolation level read committed
declare @tnActiveTable tinyint
–Clean up simple tables
truncate table ConvoySetInstances
truncate table Fragments
truncate table MessageParts
truncate table MessagePredicates
truncate table MessageProps
truncate table Parts
truncate table MessageRefCountLog1
truncate table MessageRefCountLog2
truncate table MessageRefCountLogTotals
truncate table PartRefCountLog1
truncate table PartRefCountLog2
truncate table PartRefCountLogTotals
truncate table MessageZeroSum
truncate table PartZeroSum
truncate table Spool
truncate table Instances
truncate table InstancesPendingOperations
truncate table TrackingData
truncate table ProcessHeartbeats
truncate table Tracking_Fragments1
truncate table Tracking_Parts1
truncate table Tracking_Spool1
truncate table Tracking_Fragments2
truncate table Tracking_Parts2
truncate table Tracking_Spool2
truncate table TrackingMessageReferences
— Reinserting the Restart message, for use in service recovery
INSERT INTO Spool (uidMessageID, nNumParts, nCounter, imgContext) VALUES (N’61EAA7FC-AC85-42d9-BF3E-1BED258B82BE’, 0, 0, 0xD4E0906C1849D311A24200C04F60A53302000000090000009800000050006100720074004E0061006D00650073005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F006D006500730073006100670065006100670065006E0074002D00700072006F007000650072007400690065007300000001000000010820010000000000000000000700000098000000540069006D0065007200490044005E0068007400740070003A002F002F0073006300680065006D00610073002E006D006900630072006F0073006F00660074002E0063006F006D002F00420069007A00540061006C006B002F0032003000300033002F0078006C0061006E00670073002D00720075006E00740069006D0065002D00700072006F0070006500720074006900650073000000010000000008004A000000300030003000300030003000300030002D0030003000300030002D0030003000300030002D0030003000300030002D003000300030003000300030003000300030003000300030000000)
INSERT INTO MessageRefCountLogTotals (uidMessageID, snRefCount) VALUES (N’61EAA7FC-AC85-42d9-BF3E-1BED258B82BE’, 1)
SELECT TOP 1 @tnActiveTable = tnActiveTable FROM ActiveRefCountLog WITH (ROWLOCK REPEATABLEREAD) WHERE fType = 2 OPTION (KEEPFIXED PLAN)
if (@tnActiveTable = 1)
BEGIN
if (@fLeaveActSubs <> 0)
BEGIN
INSERT INTO PredicateGroupZeroSum1 WITH (ROWLOCK)
SELECT pg.uidPredicateORGroupID, pg.uidPredicateANDGroupID
FROM PredicateGroup pg WITH (ROWLOCK), Subscription s WITH (ROWLOCK)
WHERE s.uidInstanceID IS NOT NULL AND
s.uidPredicateGroupID = pg.uidPredicateORGroupID AND
pg.fIsDistributionList = 0
OPTION (KEEPFIXED PLAN)
DELETE FROM Subscription WHERE uidInstanceID IS NOT NULL
DELETE FROM UniqueSubscription WHERE uidInstanceID IS NOT NULL
END
else
BEGIN
TRUNCATE TABLE EqualsPredicates
TRUNCATE TABLE EqualsPredicates2ndPass
TRUNCATE TABLE GreaterThanPredicates
TRUNCATE TABLE GreaterThanOrEqualsPredicates
TRUNCATE TABLE LessThanPredicates
TRUNCATE TABLE LessThanOrEqualsPredicates
TRUNCATE TABLE ExistsPredicates
TRUNCATE TABLE BitwiseANDPredicates
TRUNCATE TABLE PredicateGroup
TRUNCATE TABLE PredicateGroupZeroSum1
TRUNCATE TABLE PredicateGroupZeroSum2
truncate table Subscription
truncate table UniqueSubscription
END
END
ELSE
BEGIN
if (@fLeaveActSubs <> 0)
BEGIN
INSERT INTO PredicateGroupZeroSum2 WITH (ROWLOCK)
SELECT pg.uidPredicateORGroupID, pg.uidPredicateANDGroupID
FROM PredicateGroup pg WITH (ROWLOCK), Subscription s WITH (ROWLOCK)
WHERE s.uidInstanceID IS NOT NULL AND
s.uidPredicateGroupID = pg.uidPredicateORGroupID AND
pg.fIsDistributionList = 0
OPTION (KEEPFIXED PLAN)
DELETE FROM Subscription WHERE uidInstanceID IS NOT NULL
DELETE FROM UniqueSubscription WHERE uidInstanceID IS NOT NULL
END
else
BEGIN
TRUNCATE TABLE EqualsPredicates
TRUNCATE TABLE EqualsPredicates2ndPass
TRUNCATE TABLE GreaterThanPredicates
TRUNCATE TABLE GreaterThanOrEqualsPredicates
TRUNCATE TABLE LessThanPredicates
TRUNCATE TABLE LessThanOrEqualsPredicates
TRUNCATE TABLE ExistsPredicates
TRUNCATE TABLE BitwiseANDPredicates
TRUNCATE TABLE PredicateGroup
TRUNCATE TABLE PredicateGroupZeroSum1
TRUNCATE TABLE PredicateGroupZeroSum2
truncate table Subscription
truncate table UniqueSubscription
END
END
declare @nvcAppName nvarchar(256)
declare curse cursor for
SELECT nvcApplicationName FROM Applications
open curse
FETCH NEXT FROM curse INTO @nvcAppName
WHILE (@@FETCH_STATUS = 0)
BEGIN
exec (‘truncate table [‘ + @nvcAppName + ‘Q]’)
exec (‘truncate table [‘ + @nvcAppName + ‘Q_Suspended]’)
exec (‘truncate table [InstanceStateMessageReferences_’ + @nvcAppName + ‘]’)
exec (‘truncate table [DynamicStateInfo_’ + @nvcAppName + ‘]’)
FETCH NEXT FROM curse INTO @nvcAppName
END
close curse
deallocate curse
declare @dbname sysname
set @dbname = db_name()
BACKUP LOG @dbname WITH TRUNCATE_ONLY
GO
*/