Re: Purging Massive Tracking DB problems in BTS 2006

Tech-Archive recommends: Fix windows errors by optimizing your registry

It's not 'meaningless' to drop the views.

The views are created with the WITH SCHEMABINDING option (see view
code). When a view is created with this option, tables referenced in
views CAN NO LONGER BE TRUNCATED. Try it yourself, see SQL
documentation.

So... In order to truncate the tables, the views must be dropped. The
alternative is to use deletes, which slows down the process enormously.

Anyway - this is obviously *NOT* a production script. It is a script
we use in dire circumstances, when for whatever reason, a developers
Biztalk Database has become unmanageably large. We have been using
this script for emergency purposes for the past two years with no ill
effect, but like every other scrap of junk found on the internet, its
use at your own risk. It definitely works, and was adapted from the
SDK stored procedures, the primary difference being that this script
purges everything, end uses truncate wherever possible.



Thanks
Chuck

Stefan wrote:
Why do you need to drop the VIEWS????

This is for me not at all professional, since the views points to
tables, you must delete data in tables, it is meaningless to do so in
views???

Stéphane,
http://www.itsconsulting.fr

cduffy@xxxxxxxxxxxxxxxxxxx a écrit :

Hi

We have faced this problem numerous times. I authored this BRUTE FORCE
SCRIPT for our shop. It is based on a re-work of some SDK SQL scripts
that gracefully clean up the DTA and MESSAGEBOX. Unfortunately those
SDK scripts use deletes in a transaction, and so are no good on massive
DBS.

THIS ONE IS NOT GRACEFUL, OR RE-COVERABLE, but it reduces the DTA AND
MESSAGEBOX to as close to nothing as possible. IT IS ONLY VALID for
*BT 2004*, because it drops and re-creates views. you will need to
re-work it slightly for 2006.

Stop Biztalk before running the script
Start Biztalk after running the script

It takes a few minutes to run, so be patient.

------------------------------------------------

USE BIZTalkMsgBoxDB
GO

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 TrackedMessages
truncate table TrackingMessageReferences

GO

BACKUP LOG BiztalkMsgBoxDB with truncate_only

GO

DBCC SHRINKDATABASE (BiztalkMsgBoxDB,0)

GO

USE BIZTalkDTADB

GO


drop view [dbo].[dtav_ServiceFacts]
drop view [dbo].[dtav_FindMessageFacts]
drop view [dbo].[dtav_MessageFacts]

GO

TRUNCATE table dta_MessageFieldValues

TRUNCATE table dta_MessageInstances

TRUNCATE table dta_MessageFields

TRUNCATE table dta_MessageInOutEvents

TRUNCATE TABLE dta_DebugTrace

TRUNCATE TABLE dta_ServiceInstances

TRUNCATE TABLE dta_DebugTrace

TRUNCATE table dta_CallChain


GO


backup log BiztalkDTADb with truncate_only

GO

DBCC SHRINKDATABASE (BiztalkDTADb,0)

GO

CREATE VIEW [dbo].[dtav_ServiceFacts] WITH SCHEMABINDING
AS
SELECT [srv].[strServiceName] AS [Service/Name],
[srv].[strServiceType] AS [Service/Type],
[srvS].[strState] AS [ServiceInstance/State],
[srvI].[HRESULT] AS [ServiceInstance/ExitCode],
[srvI].[ErrorInfo] AS [ServiceInstance/ErrorInfo],
[srvI].[dtStartTime] AS [ServiceInstance/StartTime],
[srvI].[dtEndTime] AS [ServiceInstance/EndTime],
case when (DATEDIFF(second, srvI.dtStartTime, srvI.dtEndTime) >
2147483 )
then 2147483000
else DATEDIFF(millisecond, srvI.dtStartTime, srvI.dtEndTime)
end AS [ServiceInstance/Duration],
[host].[strHostName] AS [ServiceInstance/Host],
[srv].[strAssemblyName] AS [Service/AssemblyName],
[srv].[strAssemblyVersion] AS [Service/AssemblyVersion],
[srv].[dtDeploymentTime] AS [Service/DeploymentTime],
[srvI].[uidActivityId] AS [ServiceInstance/ActivityID],
[srvI].[uidServiceInstanceId] AS [ServiceInstance/InstanceID],
[srv].[uidServiceId] AS [Service/VersionGUID],
[srv].[uidNonVersionId] AS [Service/ServiceGUID],
[srvI].[uidServiceClassId] AS [Service/ServiceClassGUID]
FROM [dbo].[dta_ServiceInstances] srvI
WITH (READPAST)
LEFT JOIN [dbo].[dta_Services] srv ON srvI.uidServiceId =
srv.uidServiceId
LEFT JOIN [dbo].[dta_ServiceState] srvS ON srvI.nServiceStateId =
srvS.nServiceStateId
LEFT JOIN [dbo].[dta_Host] host ON srvI.nHostId = host.nHostId

GO

CREATE VIEW [dbo].[dtav_FindMessageFacts] WITH SCHEMABINDING
AS
SELECT [mi].[uidMessageInstanceId] AS [MessageInstance/InstanceID],
[mioe].[uidServiceInstanceId] AS [ServiceInstance/InstanceID],
[mioe].[uidActivityId] AS [ServiceInstance/ActivityID],
[si].[uidServiceClassId] AS [Service/ServiceClassGUID],
[sn].[strSchemaName] AS [MessageInstance/SchemaName],
[ms].[strStatus] AS [Event/Direction],
[po].[strPortName] AS [Event/Port],
[mioe].[dtTimestamp] AS [Event/Timestamp],
[mi].[nPartCount] AS [MessageInstance/PartCount],
[mi].[nMessageSize] AS [MessageInstance/Size],
[tr].[strAdapter] AS [Event/Adapter],
[ds].[strDecryptionSubject] AS [Event/DecryptionCertificate],
[ss].[strSigningSubject] AS [Event/Signature],
[url].[strUrl] AS [Event/URL],
[pn].[strPartyName] AS [Event/Party],
[svcs].[strServiceName] AS [ServiceInstance/ServiceName],
[mfv].[vtValue] AS [FieldValue],
[mf].[nMessageFieldsId] AS [FieldId]
FROM [dbo].[dta_MessageInOutEvents] mioe
WITH (READPAST)
LEFT JOIN [dbo].[dta_MessageInstances] mi ON mi.uidMessageInstanceId =
mioe.uidMessageInstanceId
LEFT JOIN [dbo].[dta_Adapter] tr ON tr.nAdapterId = mioe.nAdapterId
LEFT JOIN [dbo].[dta_SchemaName] sn ON sn.nSchemaId = mi.nSchemaId
LEFT JOIN [dbo].[dta_MessageFieldValues] mfv ON
mfv.uidMessageInstanceId = mi.uidMessageInstanceId
LEFT JOIN [dbo].[dta_MessageFields] mf ON
mf.nMessageFieldsId = mfv.nMessageFieldsId
LEFT JOIN [dbo].[dta_MessageStatus] ms ON mioe.nStatus =
ms.nMessageStatusId
LEFT JOIN [dbo].[dta_DecryptionSubject] ds ON
ds.nDecryptionSubjectId = mioe.nDecryptionSubjectId
LEFT JOIN [dbo].[dta_SigningSubject] ss ON ss.nSigningSubjectId =
mioe.nSigningSubjectId
LEFT JOIN [dbo].[dta_Url] url ON url.nUrlId = mioe.nUrlId
LEFT JOIN [dbo].[dta_PartyName] pn ON pn.nPartyId = mioe.nPartyId
LEFT JOIN [dbo].[dta_ServiceInstances] si ON mioe.uidActivityId =
si.uidActivityId
LEFT JOIN [dbo].[dta_Services] svcs ON si.uidServiceId =
svcs.uidServiceId
LEFT JOIN [dbo].[dta_PortName] po ON po.nPortId = mioe.nPortId

GO

CREATE VIEW [dbo].[dtav_MessageFacts] WITH SCHEMABINDING
AS
SELECT [dsn].[strSchemaName] AS [MessageInstance/SchemaName],
[st].[strStatus] AS [Event/Direction],
[evt].[dtTimestamp] AS [Event/Timestamp],
[tr].[strAdapter] AS [Event/Adapter],
[url].[strUrl] AS [Event/URL],
[ds].[strDecryptionSubject] AS [Event/DecryptionCertificate],
[ss].[strSigningSubject] AS [Event/Signature],
[srvI].[uidActivityId] AS [ServiceInstance/ActivityID],
[srvI].[uidServiceInstanceId] AS [ServiceInstance/InstanceID],
[msgI].[uidMessageInstanceId] AS [MessageInstance/InstanceID],
[evt].[nEventId] AS [Event/EventID],
[msgI].[nPartCount] AS [MessageInstance/PartCount],
[msgI].[nMessageSize] AS [MessageInstance/Size],
[pn].[strPartyName] AS [Event/Party],
[po].[strPortName] AS [Event/Port]
FROM [dbo].[dta_MessageInOutEvents] evt
WITH (READPAST)
LEFT JOIN [dbo].[dta_ServiceInstances] srvI ON evt.uidActivityId =
srvI.uidActivityId
LEFT JOIN [dbo].[dta_MessageInstances] msgI ON
msgI.uidMessageInstanceId = evt.uidMessageInstanceId
LEFT JOIN [dbo].[dta_MessageStatus] st ON st.nMessageStatusId =
evt.nStatus
LEFT JOIN [dbo].[dta_Adapter] tr ON tr.nAdapterId = evt.nAdapterId
LEFT JOIN [dbo].[dta_SchemaName] dsn ON dsn.nSchemaId = msgI.nSchemaId
LEFT JOIN [dbo].[dta_SigningSubject] ss ON ss.nSigningSubjectId =
evt.nSigningSubjectId
LEFT JOIN [dbo].[dta_DecryptionSubject] ds ON ds.nDecryptionSubjectId =
evt.nDecryptionSubjectId
LEFT JOIN [dbo].[dta_Url] url ON url.nUrlId = evt.nUrlId
LEFT JOIN [dbo].[dta_PartyName] pn ON pn.nPartyId = evt.nPartyId
LEFT JOIN [dbo].[dta_PortName] po ON po.nPortId = evt.nPortId

GO



Ruskinses@xxxxxxxxxxxxxx wrote:
ok the largest tables in this database are:

dbo.dta_MessageInOutEvents - 30Gb
dbo.dta_ServicesInstances - 8.1Gb
dbo.dta_DebugTrace - 2.9Gb

Now trying to figure out what these do ...


Stefan wrote:
Hello,

In fact what you can do to see the equivalent of TaskPad on the SQL
Server 2005 is:

In SQL Server Management Studio, Select the given Database, then select
Report, and you can see all the data concerning the given Database.

I hope that this information will help you ...

Do you have any partnerships with Microsoft, they can provide you with
the truncate tables scripts. Microsoft do not recommand you to use
something on the BizTalk Databases unless they provide you with their
Scripts

Best regards;
Stéphane
http://www.itsconsulting.fr


Ruskinses@xxxxxxxxxxxxxx a écrit :

thanks for the reply Stéphane

You are correct the default maintenance jobs did not get configured at
first and now they dont appear to be able to handle the size.

Where do i find the Task pad view in SQL 2005 ?

looking at microsoft support, the hotfix to shrink the database is for
Biztalk 2004, so i dont know how well this would work for Biztalk 2006.

Stefan wrote:
Hello,

I had the same problem several months ago in BTS2004, Your problem is
that you didn't activate the SQL jobs that delete old messages, you
have two one for the MsgBox and the other for the DTADb.

Now once you are having a Database of over 40Go, then it is difficult
to use these jobs. because they delete Data with where clauses and use
temp tables and so on

What you need to do is purge hardly the Database, by using truncate
tables. What you can do is View the Task Pad of SQL Server and see what
are the tables that have a lot of Data and truncate them...

However what I strongly recommand is that you see microsoft Website for
KB indicating your problem. Microsoft have the scripts that do the
truncate for you. Once you do that, PLEASE activate your SQL Jobs

Best regards,
Stéphane MALTI
http://www.itsconsulting.fr






Ruskinses@xxxxxxxxxxxxxx a écrit :

Hello

I have a development BTS server that has been running for the last few
months, this has been used as a test bed for a customer for about the
last 5 months. The Tracking database has grown to over 40Gb in size and
this is about half of the hard drive space in this server.

The Problem i am having is that the built in Archive and purge job does
not seem to do anything, even after i have modified the job to use the
purge stored procedure only. The job executes but never completes. I
have also tried the process where you stop all the services and run
another SP to purge all completed instances from the database, however
i left this running for about a half hour and nothing appeared to be
happening.

Is there another process i can use to clear down this database? I dont
think the tracking database is even used that much in our solution so i
would like to be able to save the data to an alternate location.

Many Thanks

.

Relevant Pages

  • Re: Purge Interchange DTA - HELP!!!
    ... Have you set up the "Backup BizTalk Databases" script? ... BACKUP LOG BizTalkDtaDB WITH TRUNCATE ONLY ... Note that this will truncate the log file of the DB - if your backup strategy relies on it, you are in trouble and should back it up beforehand. ... My InterchangeDTA database has become so large that the ...
    (microsoft.public.biztalk.server)
  • Re: Purging Massive Tracking DB problems in BTS 2006
    ... MESSAGEBOX to as close to nothing as possible. ... Stop Biztalk before running the script ... truncate table ConvoySetInstances ... and you can see all the data concerning the given Database. ...
    (microsoft.public.biztalk.general)
  • Re: removing all data from all users table
    ... You'll still have issues with foreign keys but you can do this: ... EXEC sp_msForEachTable 'TRUNCATE TABLE ?' ... Or you can generate a script this way: ... You could also consider wiping out the database and re-creating it. ...
    (microsoft.public.sqlserver.server)
  • Re: How to truncate?
    ... you can't truncate a table that is referenced by any ... Another way would be to script out all the data from the production database ...
    (microsoft.public.sqlserver.programming)
  • Re: Syntax for embedded sql
    ... I am trying to run my script. ... It does the truncate table part ... SQL> select countfrom coboltest; ...
    (comp.lang.cobol)