How To Delete Log Import Data by Using a SQL Script (331071)

MORE INFORMATION

When you use Site Server Analysis with Microsoft SQL Server, and you perform a delete operation by using Import History Manager in Usage Import, indexes are created before the delete operation occurs. This is intended to improve performance. However, it may take a long time to create indexes if you have a large database. You can use the following SQL script instead of Import History Manager to delete log import data before a specified date:

-- The script helps you delete log import data.
-- You need to specify the date (YYYY-MM-DD).
-- All imports prior to that date will be purged.
Declare @DateToPurge  smalldatetime
Select @DateToPurge = '2002-01-06'

Select Dateid into #TempDateID from tblImportDate where vTimeStamp < @DateToPurge
Select vTimeStamp into #TempTimeStamp from tblImportDate where vTimeStamp < @DateToPurge
Select ImportID into #TempImportID from tblImportSummary where DateID in (select Dateid from #TempDateID)

BEGIN TRAN
Delete tblImportMonth where vTimeStamp in (select vTimeStamp from #TempTimeStamp)
Delete tblImportQuarter where vTimeStamp in (select vTimeStamp from #TempTimeStamp)
Delete tblImportYear where vTimeStamp in (select vTimeStamp from #TempTimeStamp)
Delete tblInterseIP where vTimeStamp in (select vTimeStamp from #TempTimeStamp)

Delete tblIntersehistory where ImportID in (select ImportID from #TempImportID)
Delete tblIntersehistoryDetail where ImportID in (select ImportID from #TempImportID)


Delete tblImportVisit where DateID in (select DateID from #TempDateID)
Delete tblImportRequest where DateID in (select DateID from #TempDateID)
Delete tblImportADRequest where DateID in (select DateID from #TempDateID)
Delete tblImportSummary where DateID in (select Dateid from #TempDateID)
Delete tblImportDate where vTimeStamp < @DateToPurge

IF @@ERROR <>0
	BEGIN

		ROLLBACK TRAN

		PRINT 'Error! Now rolling back transactions'
	END
ELSE
	COMMIT TRAN


--select * from tblimportDate where vTimeStamp < @DateToPurge

go
Drop Table #tempdateid
go
Drop Table #tempTimeStamp
go
Drop Table #tempImportID
go