Purge BizTalkDTADb

use BizTalkDTADb
GO

Drop View dbo.dtav_ServiceFacts
Drop View dbo.dtav_MessageFacts
Drop View dbo.dtav_FindMessageFacts
Go

Truncate Table dta_CallChain
Truncate Table dta_DebugTrace
Truncate Table dta_MessageInOutEvents
Truncate Table dta_ServiceInstanceExceptions
Truncate Table dta_ServiceInstances
Truncate Table Tracking_Fragments1
Truncate Table Tracking_Parts1
Truncate Table Tracking_Spool1
Truncate Table dta_MessageFieldValues

exec sp_updatestats

USE [BizTalkDTADb]
GO

/****** Object: View [dbo].[dtav_FindMessageFacts] Script Date: 11/01/2019 14:52:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE VIEW [dbo].[dtav_FindMessageFacts] WITH SCHEMABINDING
AS
SELECT [mioe].[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],
[mioe].[nPartCount] AS [MessageInstance/PartCount],
[mioe].[nMessageSize] AS [MessageInstance/Size],
[tr].[strAdapter] AS [Event/Adapter],
[ds].[strDecryptionSubject] AS [Event/DecryptionCertificate],
[ss].[strSigningSubject] AS [Event/Signature],
[mioe].[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_Adapter] tr ON tr.nAdapterId = mioe.nAdapterId
LEFT JOIN [dbo].[dta_SchemaName] sn ON sn.nSchemaId = mioe.nSchemaId
LEFT JOIN [dbo].[dta_MessageFieldValues] mfv ON mfv.uidMessageInstanceId = mioe.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_PartyName] pn ON pn.nPartyId = mioe.nPartyId
LEFT JOIN [dbo].[dta_ServiceInstances] si ON mioe.uidActivityId = si.uidActivityId AND mioe.uidServiceInstanceId = si.uidServiceInstanceId
LEFT JOIN [dbo].[dta_Services] svcs ON si.uidServiceId = svcs.uidServiceId
LEFT JOIN [dbo].[dta_PortName] po ON po.nPortId = mioe.nPortId

GO

/****** Object: View [dbo].[dtav_MessageFacts] Script Date: 11/01/2019 14:52:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
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],
[evt].[strUrl] AS [Event/URL],
[ds].[strDecryptionSubject] AS [Event/DecryptionCertificate],
[ss].[strSigningSubject] AS [Event/Signature],
[srvI].[uidActivityId] AS [ServiceInstance/ActivityID],
[srvI].[uidServiceInstanceId] AS [ServiceInstance/InstanceID],
[evt].[uidMessageInstanceId] AS [MessageInstance/InstanceID],
[evt].[nEventId] AS [Event/EventID],
[evt].[nPartCount] AS [MessageInstance/PartCount],
[evt].[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 AND evt.uidServiceInstanceId = srvI.uidServiceInstanceId
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 = evt.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_PartyName] pn ON pn.nPartyId = evt.nPartyId
LEFT JOIN [dbo].[dta_PortName] po ON po.nPortId = evt.nPortId

GO

/****** Object: View [dbo].[dtav_ServiceFacts] Script Date: 11/01/2019 14:52:15 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
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

dbcc shrinkdatabase (BizTalkDTADb, 10)